Complex Join [message #371036] |
Tue, 15 August 2000 14:31 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
Hello,
I have a query that is getting the better of me. I am trying to join 2 tables and SELECT amounts from both tables. The problem is that 1 of the values can have nulls, and if that's the case, I would need the value from that field from THE LAST AVAILABLE month. It's probably easier to explain by viewing the table mock-ups below:
Example data for table A
id# year month amt1
1 2000 1 100
2 2000 2 null
3 2000 3 50
4 2000 4 null
5 2000 5 null
6 2000 6 300
7 2000 7 null
Example data for table B
id# year month amt1
1 2000 1 1000
2 2000 2 2000
3 2000 3 3000
4 2000 4 4000
4 2000 5 5000
5 2000 6 6000
6 2000 7 7000
Desired SELECTed data output after joins:
(What the SELECT query should produce)
id# year month amt1 amt2
1 2000 1 100 1000
2 2000 2 100 2000
3 2000 3 50 3000
4 2000 4 50 4000
5 2000 5 50 5000
6 2000 6 300 6000
7 2000 7 300 7000
I DO NOT want to use cursors, if possible. Can this be perfomed via query(s)?
Thanks for your help, Rob
|
|
|
Re: Complex Join [message #371041 is a reply to message #371036] |
Wed, 16 August 2000 13:04 |
JOHN
Messages: 182 Registered: April 1998
|
Senior Member |
|
|
You can probably use a UNION with a GROUP BY
on the id,year,mth. If you need a more detailed answer, let me know. Don't forget to allow for an 'extra' column when the value is null.
|
|
|
Re: Complex Join [message #371043 is a reply to message #371036] |
Wed, 16 August 2000 15:00 |
Paawan Ahuja
Messages: 2 Registered: August 2000
|
Junior Member |
|
|
Dear Sir,
I have a doubt. Does contain of Table B are correct, id is 4 twice.......
If the data in B is......
1 2000 1 1000
2 2000 2 2000
3 2000 3 3000
4 2000 4 4000
5 2000 5 5000
6 2000 6 6000
7 2000 7 7000
Then the desired output can be get using the following SQL script......
select aa.id, aa.year, aa.month, bb.amt1, aa.amt2
from b aa, a bb
where bb.month =
(select cc.month
from a cc
where cc.year = aa.year
and cc.month = (select max(dd.month)
from a dd
where dd.year = aa.year
and dd.month <= aa.month
and dd.amt1 is not null)
and cc.amt1 is not null )
If the data you have placed for table is correct then please let me know......I shall try other solution.
Regards,
Paawan
|
|
|