Aco
Aco

Reputation: 37

Checking If data from A column exists in B column

Basically this is what I have :

insert into A values(1689, 1709);
insert into A values(1709, 1689);
insert into A values(1782, 1709);
insert into A values(1911, 1247);
insert into A values(1247, 1468);
insert into A values(1641, 1468);
insert into A values(1316, 1304);
insert into A values(1501, 1934);
insert into A values(1934, 1501);
insert into A values(1025, 1101);

As you can see, there are 2 values to work with here. Let's call them a and b (a,b).

What I need to create is a query with condition that b must not exist in column1.

I'm kinda new to this, so among many things I try this looked like the closest answer but it doesn't do the job.

SELECT 
    a.*
FROM 
    A as a
LEFT JOIN 
    B AS b ON b.column = a.column
WHERE 
    B.column IS NULL

Upvotes: 1

Views: 2973

Answers (3)

TommCatt
TommCatt

Reputation: 5636

Your original query has the right idea, the names just don't match your description.

select  a1.*
from    A a1
left join A a2
    on  a2.b = a1.a
where a2.b is null;

Upvotes: 0

Gabriel
Gabriel

Reputation: 3584

SELECT COL1, COL2 FROM A WHERE
COL1 NOT IN (SELECT DISTINCT COL2 FROM A)

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Assuming I'm understanding your question, one option is to use NOT EXISTS:

select col2
from A A1
where not exists (
  select 1
  from A A2
  where A1.col2 = A2.col1
)

This will return all col2 records that do no exist in col1.

Upvotes: 2

Related Questions