CODEWITHSUNDEEP
sqlsql-server
ATKiwi
ATKiwi

Reputation: 87

How to filter SQL dataset based off value in one column?

I have a table with this structure:

╔══════╦════╦═════════╗
║ Comp ║ ID ║  Desc   ║
╠══════╬════╬═════════╣
║    1 ║  1 ║ Comp1-1 ║
║    1 ║  2 ║ Comp1-2 ║
║    3 ║  2 ║ Comp3-2 ║
║    1 ║  3 ║ Comp1-3 ║
║    1 ║  4 ║ Comp1-4 ║
║    3 ║  5 ║ Comp3-5 ║
╚══════╩════╩═════════╝

The dataset I'm creating should have a unique ID.

If an ID exists in Comp1, use that Desc.

If it does not exist in Comp1, use Comp3.

End result should look like this instead:

╔══════╦════╦═════════╗
║ Comp ║ ID ║  Desc   ║
╠══════╬════╬═════════╣
║    1 ║  1 ║ Comp1-1 ║
║    1 ║  2 ║ Comp1-2 ║
║    1 ║  3 ║ Comp1-3 ║
║    1 ║  4 ║ Comp1-4 ║
║    3 ║  5 ║ Comp3-5 ║
╚══════╩════╩═════════╝

I've tried using NOT EXISTS and joining with a subquery but I'm not sure what to Join on.

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Using not exists, it looks like:

select t.*
from t
where t.descr like 'Comp1-%' or
      not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.descr like 'Comp1-%'
                 );

Upvotes: 1

Related Questions