Creation K Creation K - 6 months ago 13
SQL Question

How can I check if each record of a database has a similar record for all columns except one?

I have a table with records as below:

enter image description here

I need to verify that for each combination of Number, Name, if the Code is null then there is another record with same Number, Name which has Code as 1. There can be a record with Code 1 but no corresponding Code null.

I tried

select t1.name,t1.number,t1.code, t2.code
from
(select distinct name,number,code from dummy
where code is null order by name, number, code) t1,
(select distinct name,number,code from dummy
where code is not null order by name, number, code) t2
where t1.name=t2.name and t1.number=t2.number


My idea was to check if for every null in t1.code there is a 1 in t2.code. But I see repetitions in my result. Can anyone please help?

mo2 mo2
Answer

If you are just trying to verify and make sure there are none (or find the ones which don't have a code = 1) then you can simply do this:

select number, name from table where code is NULL
minus
select number, name from table where code = 1