SQL Query

[ekta22]

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?

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]

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

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

 

[ekta22]

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]

Perhaps this ?

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