Full outer join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Full outer join problem

Author

Topic

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-11-07 : 17:48:33

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

30421 Posts

Posted - 2006-11-07 : 18:00:13

Your sample data does make any sense, compared to the wanted output.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-07 : 18:05:14

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 18:09:18

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources