Question In JOIN

[logic4fun]

Hi all
I am working with the following tables to create a join..and running into some problems.. the table layouts are

Table 1
------------------------------------------------
Grp Person Time Amt.Spent
------------------------------------------------
0 A 9.30 10
0 A 10.30 20
1 B 10.30 21
2 C 11.30 15
-----------------------------------------------

Table 2
------------------------------------------------
Grp Person Time Avg.Spent Amt.Left
------------------------------------------------
0 A 10.30 0 15
0 X 10.30 20 0
1 B 16.30 0 20
2 C 11.30 0 18
2 X 12.30 30 0
-----------------------------------------------

I need the final table as

------------------------------------------------
Grp Person Time Amt.Spent Amt.Left Avg.Spent
------------------------------------------------
0 A 9.30 10 - -
0 A 10.30 20 15 -
0 X 10.30 - - 20
1 B 10.30 21 - -
1 B 16.30 - 20 -
2 C 11.30 15 - 18
2 X 12.30 - - 30
-----------------------------------------------


In Brief..i need all the rows of table 1 and rows of table 2 and matching rows fitted with the required columns.

I am trying the following way but...doesnt really solve the purpose

select A.Grp, A.Person, A.Time,A.AmtSpent,B.AvgSpent,B.AmtLeft
from Table1 A
FULL OUTER JOIN
Table2 B
ON
A.Grp = B.Grp
and A.Person = B.Person
and A.Time = B.Time;


The Above Query is not working fine..ANy SUGGESTIONS..

Thanks in advance
Logic4Fun

 

[PruSQLer]

Your query should return the rows you're looking for. You'll get 2 matches on the inner join and then it will drag in the 2 unmatched rows from Table 1 and 3 rows from Table 2. But you're only asking for A.Grp, A.Person, A.Time so when you have the unmatched Table 2 rows, those fields will be nulls. You could fix that by saying -
select *
from Table1 A FULL OUTER JOIN Table2 B
etc.

Or if you want to make it look a little prettier you could use a table expression to do this:

With Temp (A_Grp,A_Pers,A_Time,B_Grp,B_Pers,B_Time,
Amt_Spent,Avg_Spent,Amt_Left)
As (
select *
from Table1 A FULL OUTER JOIN Table2 B
ON A.Grp = B.Grp
and A.Person = B.Person
and A.Time = B.Time )

Select case when A_grp is null then b_Grp else A_Grp end
, case when A_Pers is null then b_Pers else A_Pers end
, case when A_time is null then b_time else A_time end
, Amt_Spent , Avg_Spent , Amt_Left
From Temp


Hope this helps.