By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

Problem with a select statement

P: 9
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

Expand|Select|Wrap|Line Numbers
  1. SELECT     dbo.Delphi_Forecast_Rooms.BUS_ID, dbo.Delphi_Forecast_Rooms.[Room Night], dbo.Delphi_Forecast_Rooms.Singles, 
  2.                       dbo.Delphi_Forecast_FB.[Booking ID], dbo.Delphi_Forecast_FB.Event_Start_date, dbo.Delphi_Forecast_FB.Lunch
  3. FROM         dbo.Delphi_Forecast_Rooms FULL OUTER JOIN
  4.                       dbo.Delphi_Forecast_FB ON dbo.Delphi_Forecast_Rooms.BUS_ID = dbo.Delphi_Forecast_FB.[Booking ID]
  5. WHERE     (dbo.Delphi_Forecast_Rooms.BUS_ID = 81380)
This Returns
Bus_id Room Night Singles Booking Id Event_Start_Date 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_Date 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
Feb 17 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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
Feb 17 '08 #2

P: 9
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_date], 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
Feb 17 '08 #3

Delerna
Expert 100+
P: 1,134
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

Expand|Select|Wrap|Line Numbers
  1. select [Booking ID] as ID, [Event StartDate] as DTE from Delphi_Forecast_FB
  2. union
  3. select BUS_ID,[Room Night] from Delphi_Forecast_Rooms
  4.  
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

Expand|Select|Wrap|Line Numbers
  1. SELECT     c.BUS_ID, c.[Room Night], c.Singles, 
  2.                       b.[Booking ID], b.Event_Start_date, b.Lunch
  3. FROM
  4. (select [Booking ID] as ID, [Event StartDate] as DTE from Delphi_Forecast_FB
  5. union
  6. select BUS_ID,[Room Night] from Delphi_Forecast_Rooms)a
  7. left join dbo.Delphi_Forecast_FB b on a.ID=b.[Booking ID] and a.Dte=b.Event_Start_date
  8. left join Delphi_Forecast_Rooms c on a.ID=c.BUS_ID and a.Dte=c.[Room Night]
  9.  
That is straight out of my head so it hasn't been tested for correctness
Feb 17 '08 #4

P: 9
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
Feb 18 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.