INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query another table for data

Query another table for data

(OP)
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

RE: Query another table for data

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.

RE: Query another table for data

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  

RE: Query another table for data

(OP)
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
 

RE: Query another table for data

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

RE: Query another table for data

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

RE: Query another table for data

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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close