Outer join problem

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"

There are a few issues here, beginning with the fact that the row of data:

ADC M00 Acute 200 null

Doesn't exist in your original data set. ADC has only the following values:

ADC C2 M10 $300 Severe

I think you're looking for a UNION query, or perhaps a subquery for the first set of data. Books Online has some great examples of those.

Buck Woody