SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Full outer join problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitasid
Yak Posting Veteran

New Zealand
51 Posts

Posted - 11/07/2006 :  17:48:33  Show Profile  Reply with Quote
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"

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/07/2006 :  18:00:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Your sample data does make any sense, compared to the wanted output.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/07/2006 :  18:05:14  Show Profile  Reply with Quote
Your sample data doesn't make sense, but here is my closest guess, you should be able to figure what you want if this isn't exactly it.
DECLARE @Part1 TABLE
(DHBService varchar(10),
 PU varchar(10),
 Budget decimal(10,2),
 Admission varchar(10))

INSERT @Part1
SELECT 'ABC', 'M00', 200, 'Acute' UNION ALL
SELECT 'ADC', 'M10', 300, 'Severe'

DECLARE @Part2 TABLE
(DHBService varchar(10),
 PU varchar(10),
 Admission varchar(10),
 Actuals decimal(10,2))

INSERT @Part2
SELECT 'ABC', 'M10', 'Severe', 412.88 UNION ALL
SELECT 'ADD', 'M12', 'Acute', 333

SELECT coalesce(P1.DHBService, P2.DHBService) AS DHBService, 
	coalesce(P1.PU, P2.PU) AS PU,
	coalesce(P1.Admission, P2.Admission) AS Admission,
	P1.Budget, P2.Actuals
FROM @Part1 P1
FULL JOIN @Part2 P2 ON P1.DHBService = P2.DHBService AND P1.PU = P2.PU AND P1.Admission = P2.Admission
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/07/2006 :  18:09:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't think he can. If he can't even provide sound sample data and valid output based on the provided data...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000