Reputation: 37
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
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
Reputation: 3584
SELECT COL1, COL2 FROM A WHERE
COL1 NOT IN (SELECT DISTINCT COL2 FROM A)
Upvotes: 1
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