• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Data from table1 where no match on Table2

I would like to write a query which bring back all the data from table1 where there is match in table2.

The match uses a primary key called TableJoinKey

can anybody help.

Thanks
0
Mr_Shaw
Asked:
Mr_Shaw
  • 4
  • 3
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simply join:
select t1.*
  from table1 t1
 where exists ( select null from table2 t2 where t2.TableJoinKey = t1.tableJoinkey ) 

Open in new window

0
 
Mr_ShawAuthor Commented:
what would happen if i used a left or right join
0
 
RiteshShahCommented:
you can use this one also.

select t1.* from table1 t1 left join table2 t2 on t1. TableJoinKey  =t2. TableJoinKey  where t2.fieldone=NULL
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RiteshShahCommented:
above query will return only records not match with table2, if you want records with match, use this one.


select t1.* from table1 t1 left join table2 t2 on t1. TableJoinKey  =t2. TableJoinKey  where t2.fieldone is not NULL
0
 
RiteshShahCommented:
you can use AngelIII's solution also,

for records match:

select t1.*
  from table1 t1
 where exists ( select null from table2 t2 where t2.TableJoinKey = t1.tableJoinkey )


for records not match:

select t1.*
  from table1 t1
 where not exists ( select null from table2 t2 where t2.TableJoinKey = t1.tableJoinkey )
0
 
Mr_ShawAuthor Commented:
ok i'll have a play arround...

Please could someone tell me what would happen if I used a left or right join.
0
 
RiteshShahCommented:
if you use left join than you will get all records from table 1 and records doesn't match with table1, will come as NULL in table2. have a look at following query.

select t1.* from table1 t1 left join table2 t2 on t1. TableJoinKey  =t2. TableJoinKey

reverse process than above if you use right join.
0
 
Mr_ShawAuthor Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now