3

I have a table with columns ID, TYPE, DATE, ... and I need all data sets with TYPE <> 'A' (case 1) and the data sets with TYPE = A if there is no TYPE = B with the same ID (case 2). The first case is easy so I think to use UNION to combine the first and the second case. But I have no idea how to select the second case. I'm experimenting with self joins like:

SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B'

This way I get the sets which I don't want. I think I need a subquery but I have no idea in which direction I should work.

Maybe something like this:

SELECT *
FROM MY_TABLE TAB1
WHERE TAB1.TYPE <>'A'
UNION
SELECT *
FROM MY_TABLE TAB1
NOT IN
(SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B')

Example:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  A   | ...
2  |  B   | ...
3  |  A   | ...

I need row 1 (TYPE <> 'A'), row 3 ( TYPE <> 'A') and row 4 (there is no set with same ID and TYPE = 'B'). I don't want row 2, because TYPE = 'A' and there is a data set with same ID and TYPE = 'B'. So the result should be:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  B   | ...
3  |  A   | ...
6

Use NOT EXISTS:

Declare @T table (id int, [type] varchar(5))
insert into @t(id,[type]) values(1,'B'),(2,'A'),(2,'B'),(3,'A')

SELECT *
FROM @T TAB1
WHERE [TYPE] <> 'A'

UNION ALL

SELECT *
FROM @t TAB1
WHERE [type] = 'A'
    AND NOT EXISTS (
        SELECT *
        FROM @t
        WHERE id = TAB1.id
            AND [type] = 'B'
        )

The above uses UNION ALL (simple concatenation), which is typically more efficient than duplicate-removing UNION [DISTINCT]. Two sets, one with type = A and the other with type <> A cannot possibly overlap, so concatenation is safe here.

You could also express the logic as WHERE [TYPE] <> 'A' OR ([TYPE] = 'A' AND NOT EXISTS ...) but check which method gives you better performance (complex disjunctions do not always optimize well).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.