outer join

[abe6162]

I need help with a outer join.

I have a table called raw_data_info ..
raw_data_id -- raw_data_name
1 -- net income
2 -- net assets


I have another table called raw_data
raw_data_id -- report_date -- amount
1 -- 2/28/2000 -- 50


I need to write a sql statement that will return the below

raw_data_name amount
net income 50
net assets

I want a blank for net assets.

Is this possible?

 

[PHV]

A starting point:
SELECT I.raw_data_name, D.amount
FROM raw_data_info I LEFT JOIN raw_data D ON I.raw_data_id = D.raw_data_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[abe6162]

Thanks for you response. I tired this..

SELECT I.mdy_raw_data_name, D.amount
FROM raw_data_info AS I LEFT JOIN raw_data AS D ON I.raw_data_id=D.raw_data_id
WHERE report_date=#2/28/1999#;

and it returned me only the values that were in the raw_data table.
I need null to return for all the raw_data_id not in raw_data table for a specific date.

 

[PHV]

Replace this:
WHERE report_date=#2/28/1999#
By this:
WHERE (report_date=#2/28/1999# Or report_date Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[abe6162]

I am sorry. I left one column out. I didn't think it mattered, but I see it does now.

The tables should be -- raw_data_info
raw_data_id -- raw_data_name
1 -- net income
2 -- net assets


I have another table called raw_data
raw_data_id -- report_date -- cmpy_num --- amount
1 -- 2/28/2000 -- 9999 -- 50
1 -- 2/28/2000 -- 8888 -- 100

I tired --

SELECT I.mdy_raw_data_name, D.amount
FROM raw_data_info AS I LEFT JOIN raw_data AS D ON I.raw_data_id=D.raw_data_id
WHERE (report_date=#2/28/1999# Or report_date Is Null)

Is there any way to return --
raw_data_name -- amount -- cmpy_num
net income -- 50 -- 9999
net assets -- -- 9999
net income -- 100 -- 8888
net assets -- -- 8888

 

[PHV]

How is "net assets" related to cmpy_num ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[abe6162]

Each cmpy_num has a net assets and net income. But not all cmpy_num reports net assets