selecting one and/or both

[asc4john]

Hi. I have a table column with entries like the following

TO-123
XO-234
TO-345
XO-345

I want to select TOs no matter what, XO only if there is no TO with a
matching number part.
and TO if there is a TO and XO with matching number part.
So I want from the above
TO-123, XO-234, and TO-345 (not XO-345)

 

[Guest]

Here is one way that works. Instead of three queries you could use subqueries.

selecting one count –
SELECT Right([xx],3) AS [Extract]
FROM [selecting one and or both]
GROUP BY Right([xx],3)
HAVING (((Count([selecting one and or both].xx))>1));

selecting one multi –
SELECT [selecting one and or both].xx, Right([xx],3) AS Expr1, [selecting
one count].Extract
FROM [selecting one and or both], [selecting one count]
WHERE (((Right([xx],3))=[selecting one count].[Extract]));

selecting one not multi –
SELECT [selecting one and or both].xx
FROM [selecting one and or both], [selecting one multi]
GROUP BY [selecting one and or both].xx, Right([selecting one and or
both].[xx],3), [selecting one multi].Extract
HAVING (((Right([selecting one and or both].[xx],3))<>[Extract])) OR
((([selecting one and or both].xx) Like "TO*"));