easy SQL join question! - 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

SQL Server Development (2000)

easy SQL join question!

Author

Topic

NewCents
Starting Member

19 Posts

Posted - 2006-01-12 : 12:57:59

I'm working on a problem at school. I've got to produce the incoming/outgoing reservations for all flights in the system between a beginning date and an end date.

flights table dummy data:
flights_ID, date_time, num:
1, 1/05/2006, 283
2, 1/06/2006, 224
3, 1/07/2006, 182
4, 1/12/2006, 099

reservations table dummy data:
reservations_ID, flights_ID, return_flights_ID:
22, 2, 3
23, 1, 0
24, 1, 2
25, 2, 0
26, 3, 0
27, 4, 0

So this statement on the dummy data:
select * from flights inner join reservations on flights.flights_ID=reservations.flights_ID where date_time between '1/05/2006' and '1/06/2006'
Produces:
1, 1/05/2006, 283, 23, 1, 0
1, 1/05/2006, 283, 24, 1, 2
2, 1/06/2006, 224, 22, 2, 3
2, 1/06/2006, 224, 25, 2, 0

Which is great, but I need a sql statement to produce this and the return flights for that row between those two dates. So where the dummy data looked something like this (order doesn't matter):
1, 1/05/2006, 283, 23, 1, 0
1, 1/05/2006, 283, 24, 1, 2
2, 1/06/2006, 224, 22, 2, 3
2, 1/06/2006, 224, 25, 2, 0
3, 1/07/2006, 182, 26, 3, 0

This would produce the return reservation for a row even if it fell outside of the dates. So where reservation_ID 2 had a return_flight_ID of 3, that flight ID was added to the bottom of the results.

Any help appreciated. I'm stuck!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 14:10:09

Break your problem down into parts. You have already done the first part - getting flights between those two datetime values. Your second part needs to get the return flights for each of those flights, and I do mean write it as a SEPARATE STATEMENT. (Hint-use the original query and add another join to get return flights...).
Once you have both parts debugged and working correctly, you can use the UNION statement to combine the results.

NewCents
Starting Member

19 Posts

Posted - 2006-01-12 : 15:28:43

Hmm...yeah I know it's something like this:

select flights_ID, return_flights_ID from
(select flights.flights_ID, txt_return from flights inner join reservations on flights.flights_ID=reservations.flights_ID where reservations.date_time between '1/05/2006' and '1/06/2006') inner join flights on return_flights_ID=flights_ID

But of course that doesn't work

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 15:35:43

Check out this statement:

select	*
from	(select	*
	from	flights
		inner join reservations on flights.flights_ID=reservations.flights_ID
	where	date_time between '1/05/2006' and '1/06/2006') SelectedFlights

I've taken your original statment and wrapped it as a subquery named SelectedFlights. Now you can treat the derived dataset SelectedFlights just like it is a table. You can JOIN it to another instance of flights using the relationships between flightID and returnflightID.

NewCents
Starting Member

19 Posts

Posted - 2006-01-12 : 15:45:50

Ok, cool. Before you made that last post I tried this:

select *
into #table1
from flights
inner join reservations on flights.flights_ID=reservations.flights_ID
where flights.date_time between '1/05/2006' and '1/06/2006'

Would that kind of accomplish the same thing, but I would just need to reference the temp table #table1?

NewCents
Starting Member

19 Posts

Posted - 2006-01-12 : 15:50:19

I'm still confused, I've got my temp table #table1, and I can join the contents of that table like so:
select * from #table1 inner join flights on #table1.return_flights_ID=flights.flights_ID

but that pins on the return flight information to the end of that row. I need the return flight information to be in a new row. Hmm...

NewCents
Starting Member

19 Posts

Posted - 2006-01-12 : 15:57:28

All I need is some sort of an if statement. So if that row has a return_flights_ID listed, then a new row needs to show below it with the flight information.

Any ideas?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 16:49:31

You can do this with a temp table or a derived table. Derived table is more efficient, but is perhaps needlessly confusing you. So...

select * from #table1 inner join flights on #table1.return_flights_ID=flights.flights_ID

Now, instead of doing "Select *" (never good coding practice), list the columns you want to return in both your original query and this second query. The columns returned from each statement should match eachother and be in the same order.

You should run them and get two different datasets in the same format. If you get this to work succesfully, then use the UNION operator to join them together to produce a single dataset.

NewCents
Starting Member

19 Posts

Posted - 2006-01-12 : 16:52:23

ok, it works real good, and thank you for your help. I'm still having an issue with one small thing. The union will only produce data if I don't specify the column names. So if I do a union like this:
select * from flights where flights_ID=1
UNION ALL
select * from flights where flights_ID=2

That puts a table together just fine with the results. But when I do this:
select flight_number from flights where flights_ID=1
UNION ALL
select flight_number from flights where flights_ID=2

SQL query says that the command completed successfully, but no results are shown?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 23:30:21

That makes absolutely no sense at all. Are you SURE that is the EXACT code you used? Why don't you send us the DDL for the table along with a half-dozen sample records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources