Joining Derived Tables

[Peppi]

Hi,

I have a query that contains two derived tables. Is there a way for me to join these two derived tables together, or to accomplish this in a different way? I need the records from both derived tables to be returned by my query.
Thx.

 

[bborissov]

Of course:

SELECT ......
FROM (SELECT .....
             FROM ....
             WHERE....) Tbl1
JOIN (SELECT ....
             FROM ....
             WHERE....) Tbl2
ON Tbl1.PK = Tbl2.FK

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Gooser]

Yes, you can join the derived tables.

--Gooser

 

[SQLSister]

Yes you can join derived tables. In using derived tables the critical piece is that you must give each an alias and they must both containthe field(s) you want to join on), See code sample below:

select a.field1, b.field2
from 
(select field1 from table1 where type_id = 100) a
join
(select field1, field2 from table2 where Datefield > '2006-01-01') b
on a.field1 = b.field1

Questions about posting. See faq183-874

 

[Peppi]

OK, what if I'm already joining both of the derived tables to other tables? Here's my fairly lengthy query to show what I mean:

SELECT r.RfsNo, (SUM(s1.Hours) + SUM(s1.OTHours)) AS Monday,
(SUM(s2.Hours) + SUM(s2.OTHours)) AS Tuesday, (SUM(s3.Hours) + SUM(s3.OTHours)) AS Wednesday,
(SUM(s4.Hours) + SUM(s4.OTHours)) AS Thursday, (SUM(s5.Hours) + SUM(s5.OTHours)) AS Friday,
(SUM(s6.Hours) + SUM(s6.OTHours)) AS Saturday, (SUM(s7.Hours) + SUM(s7.OTHours)) AS Sunday
FROM RfsTrn r, RfsTrn r1
RIGHT OUTER JOIN (SELECT RfsNo, StaffDate, RFSStage, BillingCode, Staff
FROM StaffHour
WHERE Staff = 'DONNAP' AND StaffDate BETWEEN 20060116 AND 20060122) b ON b.RfsNo = r1.RfsNo
LEFT OUTER JOIN StaffHour s1 ON b.RfsNo = s1.RfsNo AND s1.StaffDate = 20060116 AND b.RFSStage = s1.RFSStage AND b.BillingCode = s1.BillingCode AND b.Staff = s1.Staff AND b.StaffDate = s1.StaffDate
LEFT OUTER JOIN StaffHour s2 ON b.RfsNo = s2.RfsNo AND s2.StaffDate = 20060117 AND b.RFSStage = s2.RFSStage AND b.BillingCode = s2.BillingCode AND b.Staff = s2.Staff AND b.StaffDate = s2.StaffDate
LEFT OUTER JOIN StaffHour s3 ON b.RfsNo = s3.RfsNo AND s3.StaffDate = 20060118 AND b.RFSStage = s3.RFSStage AND b.BillingCode = s3.BillingCode AND b.Staff = s3.Staff AND b.StaffDate = s3.StaffDate
LEFT OUTER JOIN StaffHour s4 ON b.RfsNo = s4.RfsNo AND s4.StaffDate = 20060119 AND b.RFSStage = s4.RFSStage AND b.BillingCode = s4.BillingCode AND b.Staff = s4.Staff AND b.StaffDate = s4.StaffDate
LEFT OUTER JOIN StaffHour s5 ON b.RfsNo = s5.RfsNo AND s5.StaffDate = 20060120 AND b.RFSStage = s5.RFSStage AND b.BillingCode = s5.BillingCode AND b.Staff = s5.Staff AND b.StaffDate = s5.StaffDate
LEFT OUTER JOIN StaffHour s6 ON b.RfsNo = s6.RfsNo AND s6.StaffDate = 20060121 AND b.RFSStage = s6.RFSStage AND b.BillingCode = s6.BillingCode AND b.Staff = s6.Staff AND b.StaffDate = s6.StaffDate
LEFT OUTER JOIN StaffHour s7 ON b.RfsNo = s7.RfsNo AND s7.StaffDate = 20060122 AND b.RFSStage = s7.RFSStage AND b.BillingCode = s7.BillingCode AND b.Staff = s7.Staff AND b.StaffDate = s7.StaffDate,
RfsTrn r2
RIGHT OUTER JOIN (SELECT DISTINCT RfsNo
FROM RfsNotesResource
WHERE UserId = 'DONNAP' AND ((EstimatedStartDate BETWEEN 20060116 AND 20060122))
OR (EstimatedEndDate BETWEEN 20060116 AND 20060122)) a ON a.RfsNo = r2.RfsNo
WHERE r.RfsNo = r1.RfsNo OR r.RfsNo = r2.RfsNo
GROUP BY r.RfsNo
ORDER BY r.RfsNo

I need to return RfsNo's in the StaffHour table that meet certain criteria. These records will have hours information. I also need to return those RfsNo's in the RfsNotesResource table with certain criteria that are not already returned by the StaffHour table. The records in the RfsNotesResource table will not have hours information, so those fields should be NULL. This query is not currently doing that. It is showing values for the hours fields when it should be NULL.

I also need to return RfsNo in a single column, so I don't want to return b.RfsNo and a.RfsNo. Rather I need to combine them and just return it in one field. This is why I am using intermediate tables to join to the derived tables and then joining both to the master RfsTrn table.

I think what I need to ultimately do is a full outer join of the two separate results of joining the derived table with its RfsTrn counterpart.

Am I making this more complicated than it needs to be?

 

[bborissov]

Peppi, please round your code portion with [ code ] and [ /code ] (w/o spaces} tags.
i.e.
[ code ] (w/o spaces)
SELECT ....

[ /code ] (w/o spaces)
That will make code more readable.
Did you try to read and understand your posted code after you posted it here? Keep in mind that we didn't know your code.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

Your query will certainly be syntactically correct, whether it does what you want it to do is another issue. It is easier to see what you want if you givce us sample data and sample results.

Questions about posting. See faq183-874