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.
| Author |
Topic |
|
Edoode
Starting Member
6 Posts |
 Posted - 2004-10-26 : 09:51:16
|
| Hi,I have the following tables: stops(id int) and times(stop int, time int, trip int)filled withid--456stop time trip---- ---- ----4 10 1 5 11 16 12 14 10 25 11 2 5 11 3 6 12 3and I need the full outer join set like:stop time trip---- ---- ----4 10 1 5 11 16 12 14 10 25 11 2 6 null 24 null 35 11 3 6 12 3anyone? -Edo-------------------------Swords don't run out of ammo |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-26 : 09:54:36
|
| ummm... i think you need to explain a little bit more about WHY you wish those results to be returned. You have not indicated any relationship between your two tables, and for the life of me I cannot figure out how or why you expect to combine those two tables to produce that result.- Jeff |
 |
|
|
Edoode
Starting Member
6 Posts |
Posted - 2004-10-26 : 10:24:38
|
| Hi, Think public transport. Table 'stops' are all stops on a certain busline. Table 'times' are passingtimes for each stop. But not all busses stop at each stop, hence the nulls. So, in my example, trip number 1 passes stop 4 at 06:10, stop 5 at 06:11 and stop 6 at 06:12 (the hour is just an example), but trip 2 doesn't stop at stop 6, while trip 3 doesn't stop at stop 4Regards, -Edo-------------------------Swords don't run out of ammo |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-10-26 : 11:11:44
|
| [code]SELECT stops.id AS stop, times.time, trips.tripFROM stopsCROSS JOIN (SELECT DISTINCT trip FROM times) AS tripsLEFT JOIN times ON stops.id = times.stop AND trips.trip = times.trip[/code] |
|
|
|
Edoode
Starting Member
6 Posts |
Posted - 2004-10-26 : 16:56:56
|
| To quote Ted&Bill: 'Awesome'Thanks a ton :) -Edo-------------------------Swords don't run out of ammo |
|
|
|
|
|
|
|
|
LEFT JOIN with GROUP BY