Problem with a select statement

[Dellboymash]

I am setting up a view that pulls from two other views. I want to return the records from both views based on 1 common field but I end up with the Catesian product of these records

There are two valid records in each table

SELECT     dbo.Delphi_Forecast_Rooms.BUS_ID, dbo.Delphi_Forecast_Rooms.[Room Night], dbo.Delphi_Forecast_Rooms.Singles, 
                      dbo.Delphi_Forecast_FB.[Booking ID], dbo.Delphi_Forecast_FB.Event_Start_date, dbo.Delphi_Forecast_FB.Lunch
FROM         dbo.Delphi_Forecast_Rooms FULL OUTER JOIN
                      dbo.Delphi_Forecast_FB ON dbo.Delphi_Forecast_Rooms.BUS_ID = dbo.Delphi_Forecast_FB.[Booking ID]
WHERE     (dbo.Delphi_Forecast_Rooms.BUS_ID = 81380)

This Returns
Bus_id Room Night Singles Booking Id Event_Start_Dat e Lunch
81380----18/02/08------10------ 81380----19/02/08---------150
81380----18/02/08------10------ 81380----20/02/08---------163
81380----19/02/08------16------ 81380----19/02/08---------150
81380----19/02/08------16------ 81380----20/02/08---------163

What I want it to return is
Bus_id Room Night Singles Booking Id Event_Start_Dat e Lunch
81380----18/02/08-------10-------Null--------Nul----------------Null
Null--------Null-------------Null-----81380-----20/02/08--------163
81380----19/02/08-------16-------81380-----19/02/08--------150

I have been trying every combination of joins but seem to missing something.

I would appreciate anyones help

[Delerna]

It is because you are joining using the Bus_ID to Booking_ID only. There are multiple dates associated with the number so you will always get a cartesian product using it alone. From what I can see, you will need to adjust the query so you can join with the date also. If not the date then you will need to provide some other field so that each join will be a unique match between the tables

[Dellboymash]

It is because you are joining using the Bus_ID to Booking_ID only. There are multiple dates associated with the number so you will always get a cartesian product using it alone. From what I can see, you will need to adjust the query so you can join with the date also. If not the date then you will need to provide some other field so that each join will be a unique match between the tables

Thanks for the quick response. I have also tried adding a join from the Room table [Room Night] and Events Table [Events_start_da te], but this will only return matches where both dates are equal (obvious!)

To briefly explain. The events table shows events that happen during a booking e.g. Lunch, Dinner, etc. the Rooms table shows the nights guest are staying.

Invaribly guests stay the night before any events happen. What I am trying to do is return the records of when Rooms Nights are incurred and when Events are incurred.

As per your example by using the date field as well. Nothing is returned for the first nights stay, because there are no events on that day.

Any workaround would be much appreciated

[Delerna]

Oh OK, I was trying to point you toward finding an answer by stating you would need to rearrange the query so that you could join using both.
So her is how I would do it
make a query to get every ID and date concerned

select [Booking ID] as ID, [Event StartDate] as DTE from Delphi_Forecast_FB
union
select BUS_ID,[Room Night] from Delphi_Forecast_Rooms

Test that to see if the results are correct. You may need to change the top query in the union to a SELECT DISTINCT ....FROM ... if there are duplicate ID,Dte combinations.
You now have a master query that you can use to left join the two table to and ensure that you get every record in both tables

SELECT     c.BUS_ID, c.[Room Night], c.Singles, 
                      b.[Booking ID], b.Event_Start_date, b.Lunch
FROM
(select [Booking ID] as ID, [Event StartDate] as DTE from Delphi_Forecast_FB
union
select BUS_ID,[Room Night] from Delphi_Forecast_Rooms)a
left join dbo.Delphi_Forecast_FB b on a.ID=b.[Booking ID] and a.Dte=b.Event_Start_date
left join Delphi_Forecast_Rooms c on a.ID=c.BUS_ID and a.Dte=c.[Room Night]

That is straight out of my head so it hasn't been tested for correctness

[Dellboymash]

Spot on

Thanks very much for your help. I had spent two weeks trying to get my head around this before you response.

You solution works perfectly