List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:April 13 2005 11:33am
Subject:Re: LEFT JOINS same data twice?
View as plain text  
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

Thread
LEFT JOINS same data twice?Chris Knipe13 Apr
  • Re: LEFT JOINS same data twice?Peter Brawley13 Apr
  • Re: LEFT JOINS same data twice?Rhino13 Apr
  • Re: LEFT JOINS same data twice?Roger Baklund13 Apr
  • Re: LEFT JOINS same data twice?Chris Knipe15 Apr
    • Re: LEFT JOINS same data twice?SGreen15 Apr
      • Re: LEFT JOINS same data twice?Peter Brawley15 Apr
      • Re: LEFT JOINS same data twice?Chris Knipe15 Apr
        • Re: LEFT JOINS same data twice?SGreen15 Apr
          • Re: LEFT JOINS same data twice?SGreen15 Apr