Home » RDBMS Server » Server Administration » Complex Join
Complex Join [message #371036] Tue, 15 August 2000 14:31 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Cursor
Next Topic: ALTER TABLE DROP COLUMN does not work correctly
Goto Forum:
  


Current Time: Fri Mar 29 03:59:51 CDT 2024