December 5, 2008 at 9:38 am
hi there,
stupid join question that I've been working on for 2hrs and cant wrap my brain around.
SELECT 0,
'HDR1',
'HDR2'
UNION
SELECT TOP 100 PERCENT
1,
SID = t.SID,
ISNULL(ppp.PT1,h.PT1),
FROMTABLE1 t
LEFT JOIN TABLE2 ppp
ON t.SID = ppp.SID
LEFT JOIN (
SELECT SID, PT1 = MAX(PT1)
FROM TABLE3
WHERE DataDate = (SELECT MAX(DataDate) FROM TABLE3)
GROUP BY SID, DataDate
) h
ON t.SID = ppp.SID
The problem that I am having is that there are two records in table1, 1 record in table 2 and 0 records in table3
I need to return the two records from table1 regardless of if it exists in table3.
HELP!?!?
EDIT: Left off the header records
December 5, 2008 at 10:33 am
The last ON clause for Table 3 is the same as the ON clause for Table 2.
Is that the problem?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2008 at 10:42 am
I agree, you are naming your nested select h but your on clause is using ppp.
December 5, 2008 at 10:43 am
Sorry, I didn't complete my thought:
SELECT 0,
'HDR1',
'HDR2'
UNION
SELECT TOP 100 PERCENT
1,
SID = t.SID,
ISNULL(ppp.PT1,h.PT1),
FROM TABLE1 t
LEFT JOIN TABLE2 ppp
ON t.SID = ppp.SID
LEFT JOIN (
SELECT SID, PT1 = MAX(PT1)
FROM TABLE3
WHERE DataDate = (SELECT MAX(DataDate) FROM TABLE3)
GROUP BY SID, DataDate
) h
ON t.SID = h.SID --change this line
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply