SQL Join problem!

[richiemac]

Hi all, Here's the problem... I've got two tables, trains and trainarrivals. Each train has a link to two arrivals records. The first link is to the previous arrival record and the d=second is to the next arrivals record. I'm trying to join the tables using, select * from train LEFT OUTER JOIN trainarrivals ON train.PreviousArrival = trainarrivals.UniqueID OR train.NextArrival = trainarrivals.UniqueID ORDER BY train.UniqueID so that when i get the trains with their arrivals links I will get one row for each train. Due to the fact that the next and previous arrivals are separate records in the trainarrivals table I get two records for each train. My question is, is there any way to do this so I get only one record with both the next and previous arrival data together. Thanks for the help in advance. Rich

[Eric Dahlvang]

select * from train
LEFT OUTER JOIN trainarrivals ta1 ON train.PreviousArrival = ta1.UniqueID
LEFT OUTER JOIN trainarrivals ta2 ON train.NextArrival = ta2.UniqueID
ORDER BY train.UniqueID

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

[richiemac]

Thanks Eric! It worked a treat. I really didn't know it would be that simple. Thanks again!