SQL Query

ekta22 · Jan 16, 2008

Hi,

I am trying to join two tables and get the result as below

Table1
ID
100
200
300

Table2
Group_ID---ID
1----------100
1----------101
1----------102
2----------200
2----------201

I have the query below so far and it gives me 100, 101, & 102 which is okay. But I want to get rid of 100 which is the matching id between table1 and table2. How to do that?

Code:

SELECT T2_2.ID FROM
TABLE1 T2_1
JOIN TABLE1 T1
ON
 T1.ID=T2_1.ID
JOIN TABLE2 T2_2
    ON T2_1.GROUP_ID=T2_2.GROUP_ID;

My result should be
ID
101
102
201

Thanks!

jaxtell · Jan 16, 2008

I'm not sure what you want. To get ids in table2 but not table1 try

Code:

select id from table2 where id not in(select id from table1);

ekta22 · Jan 16, 2008

I think I was not very clear. Let me try n explain again.

Table1
ID
100

Table2
Group_ID---Related_ID
1------------100
1------------101
1------------102

I want to display all the related ID's for ID 100 in table1 that are in the same group_id "1".
So by the above statement I should see 100, 101 & 102 in my resultset. But I want to exclude 100 from this resultset which is the link between table1 and table2.

Hope it makes sense!

PHV · Jan 16, 2008

Perhaps this ?

Code:

SELECT T2_2.ID FROM
TABLE1 T2_1
JOIN TABLE1 T1
ON
 T1.ID=T2_1.ID
JOIN TABLE2 T2_2
    ON T2_1.GROUP_ID=T2_2.GROUP_ID
   AND T2_1.ID<>T2_2.ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL Query

ekta22

IS-IT--Management

jaxtell

Programmer

ekta22

IS-IT--Management

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor