I'm new to SQL. I wanted to know, How to check the entries which are not repeated for the other entries.

Better I give one example.

I want output as

because c does not have 4, but the other values a and b do.

CodePudding user response：

You are looking for missing entries. For these to find, you must know which entries you expect. You expect to find one entry for all combinations of column1 values and column2 values. You get these with a cross join.

Now you want to select all these pairs except for those already in the table. You can use EXCEPT for this or NOT EXISTS or NOT IN. You haven't told us your DBMS, so I don't know what it features.

EXCEPT

select c1.column1, c2.column2
from (select distinct column1 from mytable) c1
cross join (select distinct column2 from mytable) c2
except
select column1, column2
from mytable
order by column1, column2;

NOT IN

select c1.column1, c2.column2
from (select distinct column1 from mytable) c1
cross join (select distinct column2 from mytable) c2
where (c1.column1, c2.column2) not in
(
    select column1, column2 from mytable
)
order by c1.column1, c2.column2;

NOT EXISTS

select c1.column1, c2.column2
from (select distinct column1 from mytable) c1
cross join (select distinct column2 from mytable) c2
where not exists
(
    select null
    from mytable
    where mytable.column1 = c1.column1
    and mytable.column2 = c2.column2
)
order by c1.column1, c2.column2;

CodePudding user response：

Try this :

SELECT DISTINCT title,id FROM table

CodePudding user response：

Provided columns are not nullable

select distinct column1
from mytable t1
where exists (
      select 1 
      from mytable t2
      where t2.column2 <> t1.column2
          and t1.column1 not in (
             select t3.column1 
             from mytable t3
             where t3.column2 = t2.column2
             )
      )

CodePudding user response：

Not tested yet, but this should work
(yet it would return multiple rows with the same col1 col2)

SELECT
    T.col1, T.col2
FROM
    table T
WHERE
    NOT EXISTS
    ( SELECT * FROM table X
      WHERE X.col2 = T.col2 AND X.col1 <> T.col1)