November 7, 2006 at 3:45 pm
Hi guys
I have got two tables which I need to join
table 1
DHBName DHBService PU Budget Admission
ABC C1 M00 $200 Acute
ADC C2 M10 $300 Severe
Table 2
DHBService PU Admission Actuals
ABC M10 Severe 412.88
ADD M12 Acute 333
The 'DHB Service ' , 'PU' and 'Admission' are common in two tables but 'budget' and 'actuals' are different
I need to combine these two tables in such a way that I have all the fields from both the table
The sample result should be like this
DHBService PU Admission Budget Actuals
ABC M10 Severe Null 412
ADC M00 Acute 200 null
What should I do
I am trying this query but not getting the desired results:-
"SELECT ISNULL(dbo.part1.DHB_service, dbo.part2.DHB_service) , ISNULL(dbo.part1.PU, dbo.part2.PU)
, ISNULL(dbo.part1.budget, 0) , ISNULL(dbo.part2.actuals, 0) , ISNULL(dbo.part1.Admission,
dbo.part2.Admission) AS Expr6
FROM dbo.part1 FULL OUTER JOIN
dbo.part2 ON dbo.part1.PU = dbo.part2.PU AND dbo.part1.DHB_service = dbo.part2.DHB_service AND dbo.part1.Admission = dbo.part2.Admission"
November 7, 2006 at 3:55 pm
November 8, 2006 at 8:57 am
1. Whilst your 3 columns correlate the data does not, what is DHBName?
2. Your sample output is not possible from the sample input you provided
Your query :-
SELECT ISNULL(a.DHB_service, b.DHB_service) AS [DHB_service],
ISNULL(a.PU, b.PU) AS [PU],
ISNULL(a.Admission, b.Admission) AS [Admission],
a.budget,
b.Actuals
FROM dbo.part1 a
FULL OUTER JOIN dbo.part2 b
ON a.DHB_service = b.DHB_service
AND a.PU = b.PU
AND a.Admission = b.Admission
ORDER BY ISNULL(a.DHB_service, b.DHB_service) ASC,
ISNULL(a.PU, b.PU) ASC,
ISNULL(a.Admission, b.Admission) ASC
Produces this result
DHB_Service PU Admission Budget Actuals
----------- -- --------- ------ -------
ABC M00 Acute 200.00 NULL
ABC M10 Severe NULL 412.88
ADC M10 Severe 300.00 NULL
ADD M12 Acute NULL 333.00
Far away is close at hand in the images of elsewhere.
Anon.
November 8, 2006 at 12:41 pm
Thanks for that David
It works fine now
cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply