SQL - Select rows with same value in one column and choose the one with specific value in other colu-CodePudding

I just started learning SQL and ran into a problem that I can't solve on my own.

I have the following table:

id	project	field
10301	A	field_10200
10302	null	field_10201
10303	A	field_10202
10400	null	field_10300
10401	null	field_10301
10500	null	field_10400
10502	B	field_10212
10505	A	field_10301
10506	B	field_10301

Now I want to output only the IDs where the field belongs to project A or null but not both.

The output should look like this:

id	project	field
10301	A	field_10200
10302	null	field_10201
10303	A	field_10202
10400	null	field_10300
10500	null	field_10400
10505	A	field_10301

CodePudding user response：

Here's the solution:

select *
  from Table t
 where t.project = 'A'
    or (    t.project is null
        and not exists(select * from Table t2
                        where t2.project = 'A' and t2.field = t.field)
       )

CodePudding user response：

If we call u the fields having A as project and v the fields having null as project, then u union v except (u intersect v) gives you the fields you want. inner join that to the original table and you'll get the desired output:

with u as
(select field from table_name where project = 'A'),
v as
(select field from table_name where project is null),
w as
(select field from u
union select field from v
except (select field from u intersect select field from v))
select id, project, t.field 
from table_name t inner join w
on t.field = w.field;

Fiddle