Query another table for data 1

[mcauliff]

I'm trying to write query that will select rows from 1 table and using that select rows from another table. The catch is that the other table may not have a matching row.

An example

Select client, site, product, family
from tab_a
where product = 'a'

on the other table (tab-b) are the values for product status and cancel date.

Select prod_status, cancel_date
from tab_b
where tab_b.client = tab_a.client
and tab_b.site = tab.a.site
and tab_b.product = tab_a.product

I tried exists and product in, but they only return the row if it is a match. If the tab_b doesn't have a match, than no row is returned.

I'm trying to do this in 1 SQL statement to use in Excel.

It is for DB2 v7

 

[papadba]

If you post a bit of sample data from both tables and what you want as the result of your query, someone may have a similar query to share.

 

[MarcLodge]

Hi,
Graeme Birchall's excellent cookbook covers this and can be found here

I'm guessing from what you've said that you need a LEFT OUTER JOIN i.e. data from one table, plus any additional data that exists for the already selected rows.

Hope this helps.

Marc

 

[mcauliff]

The data looks like this

tab_a

Client Site product family
100 1 aa m
123 2 fa m

tab_b

Client Site Product status cancel_date
123 2 fa 9 12/23/2006


The results that I want is

100 1 aa m

123 2 fa m 9 12/23/2006

I need the all rows from tab_a whether there is data in tab-b or not.

Thank you for the assistance

 

[sathyarams]

You have to use Left outer join as Marc said.

SELECT * FROM tab_a a
left outer join tab_b  b
on a.site=b.site

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[mcauliff]

Thanks to everyone for the assistance. I usually don't have to use the Join.

 

[MarcLodge]

hi mcauliff,

Great to see that you've got your problem solved. I really would urge you to put in your favourites Graeme's cookbook as it is a well written reference aid that I find myself using time and again.

Congrats for getting it sorted.

Marc

 

[Truusvlugindewind]

Reference Graeme's cookbook when a question like this comes up get you a purple star. I agree, its a great book (has been for years now).