Query to fetch Exact Rows that satisfies the below cond.

natinuk · New User Joined: 23 Nov 2006 Posts: 22 Location: India

Hi all
I have two tables as follows, I need to write a query to fetch all the ID from the Table-1 whose ID-name not equal to M12 and also should not include IDs whose Id-name has M12.(Here if we take ID 12 it has N12,M12,L12,K12 as ID-names. Since it has M12 it should not populate the ID 12.)

Table-1
ID(PK) ID-name(PK)
12 N12
12 M12
12 L12
12 K12
13 N12
16 M12
14 N14
15 M12

Table-2
ID(PK)
12
13
14
15
16
17
18
19

The Output should be from Table -1
ID ID-name
13 N12
14 N14

Thanks in advance

pyar · New User Joined: 15 May 2006 Posts: 4

SELECT ID-NAME, ID-NO FROM TABLE-1 WHERE ID-NO NOT IN (SELECT ID FROM TABLE-1 WHERE ID-NAME=?M12?)

Thanks
Pyar