List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 18 1999 12:27pm
Subject:Re: multiple join to the same table
View as plain text  
On Sat, 1999-09-18 06:26:09 +0200, Alexander Greim wrote:
> i've got a problem using select with mutiple join.
> my tables are:
> 
> CREATE TABLE jobs (
>   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
>   fachrichtung1_id int(10),
>   fachrichtung2_id int(10),
>   fachrichtung3_id int(10),
>   PRIMARY KEY (id)
> );
> 
> CREATE TABLE fachrichtungen (
>   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
>   fachrichtung varchar(100),
>   PRIMARY KEY (id)
> );
> 
> fachrichtung1_id, 2_id, 3_id in jobs is a reference to
> fachrichtungen.id.
> 
> i'm searching for a select the shows that is working.

If you want to display rows of table 'jobs' with the three ids
replaced by their names, then you need to do _three_ look-ups in table
'fachrichtung', that meaning one join with it for each id.

  SELECT jobs.id
       , f1.fachrichtung AS fachrichtung1
       , f2.fachrichtung AS fachrichtung2
       , f3.fachrichtung AS fachrichtung3
  FROM jobs
     , fachrichtungen AS f1
     , fachrichtungen AS f2
     , fachrichtungen AS f3
  WHERE jobs.fachrichtung1_id = f1.id
    AND jobs.fachrichtung2_id = f2.id
    AND jobs.fachrichtung3_id = f3.id;

The trick here is to use table aliases "AS f1" etc. to distinguish the
three instances of the same table.

The column aliases "AS fachrichtung1" etc. are not really necessary,
but otherwise the column titels of the result would be 'fachrichtung'
three times.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
multiple join to the same tableAlexander Greim18 Sep
  • Re: multiple join to the same tableMartin Ramsch18 Sep