Chris Knipe wrote:
> Hi,
>
> Is it possible to left join the same data twice?
Yes.
> TBL ONE:
> LocationID
> Location, varchar(100)
>
> TBL TWO:
> DepartureID,
> ArrivalID,
> Time
>
> SELECT tlb1.DepartureLocation AS Departure, tbl1.DepartureLocation AS
> Destination LEFT JOIN DepartureLocation ON
> tlb2.DepartureID=tlb1.LocationID LEFT JOIN DepartureLocation ON
> tbl2.ArrivalID=tbl1.LocationID ORDER BY tbl2.Time etc etc etc
>
>
> I get
>
> ERROR 1066 (42000): Not unique table/alias: 'tbl1'
The statement above looks a bit strange, there is no FROM clause, and
there seems to be 4 tables involved, named tbl1, tlb1, tbl2 and tlb2?
According to your table description above, there is no column named
"DepartureLocation"? According to the query, it seems like a table has
that name?
I'll pretend you have two tables named tbl1 and tbl2, containing the
columns you described above:
tbl1: LocationID, Location
tbl2: DepartureID, ArrivalID, Time
Then try something like this:
SELECT Departure.Location, Arrival.Location AS Destination, Time
FROM tbl2
LEFT JOIN tbl1 AS Departure ON
Departure.LocationID = DepartureID
LEFT JOIN tbl1 AS Arrival ON
Arrival.LocationID = ArrivalID
ORDER BY Time
Note that there are two different uses of alias in this statementtable
alias and column alias: the table "tbl1" is aliased twice, to
"Departure" and "Arrival". Aliasing a table is necessary to be able to
join the same table multiple times. The "Arrival.Location" column is
aliased to "Destination". This is necessary to avoid two columns from
having the same name, in this case both columns would have been named
"Location".
--
Roger
