CODEWITHSUNDEEP
sql
Jenny Tran
Jenny Tran

Reputation: 251

How to select SQL for column 1 not in column 2

I have a table with four columns. The first column ID1 has one similar row in ID2. I like to select a query to get all rows from ID2, except the identical record. For instance:

ID1 Name1 ID2 Name2
-------------------
01  A     01  A
01  A     02  B
01  A     03  C
03  C     03  C
03  C     01  A
03  C     04  G

Select ID2, Name2 From table where ID2 <> ID1

Output should be:

ID2, Name2
----------
02   B
03   C
01   A
04   G

03/C and 01 A need to be here. If I use distinct, then it exludes all identical records of ID1, which I don't want to. I am using classic ASP to select ID1 groups in SELECt option list. if I select 01, then SELECT option list for ID2 appear. I only want to see all ID2 that is not in ID1.

enter image description here

Can someone help me please? Thanks.

Upvotes: 2

Views: 4357

Answers (5)

Nikhil
Nikhil

Reputation: 3950

this will work:

select ID2,Name2 from tablename where ID1!=ID2 and Name1!=Name2

Upvotes: 1

Ariel Grabijas
Ariel Grabijas

Reputation: 1512

Your question is not entirely clear, assuming that ID1 can't equal ID2 AND name1 can equal name2 the proper answer would be:

SELECT DISTINCT ID2, Name2 FROM tableName WHERE ID2 <> ID1

You select columns ID2 and Name2 but only those where ID1 is not equal to ID2. There is also DISTINCT, it guarantees that only different values are returned.

if name1 can't equal name2 you have to use:

SELECT DISTINCT ID2, Name2 FROM tableName WHERE ID2 <> ID1 AND name1 <> name2

Upvotes: 1

forpas
forpas

Reputation: 164099

I think there is no need for explanation:

select id2, name2
from t
where (id1 <> id2) OR (name1 <> name2)

Upvotes: 5

MikeS
MikeS

Reputation: 1764

Try 'distinct'

Select distinct ID2, Name2 From table where ID2 <> ID1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you want not exists. But your result set doesn't seem to match the description of the question.

select id2, name2
from t
where not exists (select 1
                  from t t2
                  where t2.id1 = t.id2 and t2.name1 = t.name2
                 );

Upvotes: 1

Related Questions