CODEWITHSUNDEEP
sqljoin
user8530455
user8530455

Reputation:

Get data using JOIN

I have two tables having following data-

Social_Tbl

ID  Name         Value
------------------------
1   Facebook     FB
2   Orkut        OR
3   Google       GL
4   Other        OT

And Organization_tbl

ID  Organization   Name  
-----------------------------      
1   1234           Facebook
2   1234           Google
3   146            Other
4   126            Other
5   126            Facebook
6   77             Google

Here, 'Name' is the foreign key (Not ID).
I want to join these tables and get the 'Name' columns data which does not belong to organization id 1234. As follows-

Name
----
Orkut 
Other

Here, 'Orkut' and 'Other' does not belong to 1234 organization.

I tried following query for this- 

select * from Social_Tbl st
join Organization_tbl ot 
     on st.Name = ot.Name
where Organization = 1234

This query fetches Names related to 1234 i.e Facebook and Google. I want result Orkut and Other. If I replace Organization = 1234 with Organization != 1234 it returns all data from Organization_tbl.

Can somebody help me on this. This should be pretty simple, just npt able to find it out.

Upvotes: 0

Views: 37

Answers (1)

melpomene
melpomene

Reputation: 85767

Could be done with a subquery:

select st.Name
from Social_Tbl st
where not exists (
    select *
    from Organization_tbl ot
    where st.Name = ot.Name
      and ot.Organization = 1234
)

(This also returns names that don't have an entry in Organization_tbl at all.)

Upvotes: 1

Related Questions