Join tables on two seppearate rows

[thom]

Hi,

I'm wondering if there is a way (I'm using mySQL) to join the same table two times but a different row each time. Let me make an example, I have a time table where there can be one or two instructors. In the time tables I keep info about when each class (starting time, finishing time, day of week, etc.) and instructor 1 and instructor 2. To select only instructor one I would do the following:

"SELECT timetable.start,timetable.end, ... ,instructors.name FROM timetable LEFT JOIN instructors ON timetable.instructor1 = instructors.id"

OK, now I have one instructor BUT I need the other. I know it's easy doing this with the help of PHP (making one query per each row), but if I want to get all my data from this single query... how would the query look like.

Hope I'm making myself understandable 🙂

[sarahk]

using old fashioned sql

$sql = "SELECT timetable.start, timetable.end, ... ,instructors.name 
FROM timetable, instructors 
where timetable.instructor1 = instructors.id 
or timetable.instructor2 = instructors.id";

[thom]

Thanks for that, but there is still one thing that's bothering me. If there are two instructors, I'll have two rows. This is something I don't want, I only want one line per each class... any ideas anyone?

[tomhath]

You need to join with the "instructors" table twice:

SELECT timetable.start,timetable.end, ... ,in1.name, in2.name
FROM timetable t
LEFT JOIN instructors in1 ON t.instructor1 = in1.id
LEFT JOIN instructors in2 ON t.instructor2 = in2.id

[mikesgt2]

Could you give the schema, possibly with a bit of example data, and an example of the type of result set output you are looking for?

[Sxooter]

Sure, this is called an inner self-join. Lets say we have a table with employee names, employee numbers, and employee's supervisor employee numbers:

create table folks (emp text, enum text, boss text);
insert into folks values ('Goober Pyle','1234','4556');
insert into folks values ('Gomer Pyle','4556','9987');
insert into folks values ('Sgt Carter','9987','prez');

select p.emp, p.enum, b.emp, b.enum
from folks p, folks b
where p.enum=b.boss;

[thom]

tomhath: you saved my day. To be honest I really doubted this could be done, but well, thanks for proving me wrong. I had written an exact description of my problem before I saw your reply, and it took me a bit of time to do it, so I'm just gonna let it go 🙂

The following queries might have minor errors, I had to translate the names of the tables to english to make their purpose more understandable.

CREATE TABLE classes_info (
id smallint(4) unsigned NOT NULL auto_increment,
strClassname varchar(200) NOT NULL default '',
strDescription text,
intInstructor1 smallint(5) unsigned NOT NULL default '0',
intInstructor2 smallint(5) unsigned default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM

CREATE TABLE instructors (
id smallint(5) unsigned NOT NULL auto_increment,
strNickname varchar(30) NOT NULL default '',
strFullname varchar(50) NOT NULL default '',
strEmail varchar(60) default NULL,
strPhone varchar(7) default NULL,
strInfo text,
PRIMARY KEY (id)
) TYPE=MyISAM

CREATE TABLE timetable (
id smallint(5) unsigned NOT NULL auto_increment,
intWeekday tinyint(1) unsigned NOT NULL default '0',
timeTimiStart time NOT NULL default '00:00:00',
timeTimiEnd time NOT NULL default '00:00:00',
intClass smallint(4) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM

---

INSERT INTO instructors (strNickname,strFullname,strEmail,strPhone,strInfo) VALUES ('Johnny','John Doe','[e-mail]','5555555','John is one of our best staff member ...');

INSERT INTO instructors (strNickname,strFullname,strEmail,strPhone,strInfo) VALUES ('Joan','Joan Doe','[e-mail]','5556666,'Joan is the worst staff member');

insert into classes_info(strClassname,strDescription,intInstructor1,intInstructor2) VALUES ('Jogging','Jogging around','1,2);

INSERT INTO timetable (intWeekday,timTimiStart,timeTimiEnd, intClass) values (1,'08:00','09:10',1);

means: (Monday,time-starts-at-0800,time-ends-at-0910,refers-to-id-in-classes_info)

okay, if I would only have 1 instructor, the query would look like this:

SELECT
timetable.intWeekday,
CONCAT(time_format('%H:%i',timeTimiStart),'-',time_format('%H:%i',timeTimiEnd)) as timerange,
classes_info.strClassname,
instructors.strNickname
FROM
timetable
LEFT JOIN
classes_info ON
timetable.intClass = classes_info.id
LEFT JOIN
instructors ON
classes_info.intInstructor1 = instructors.id;

the results of the query would look like this somehow:

+----------+-----------+------------+-----------+
|intWeekday|timerange |strClassname|strNickname|
+----------+-----------+------------+-----------+
| 1|08:00-09:10| Jogging| Johnny|
+----------+-----------+------------+-----------+

when my desired results would look like this:
+----------+-----------+------------+-----------+------------+
|intWeekday|timerange |strClassname|strNickname|strNickname2|
+----------+-----------+------------+-----------+------------+
| 1|08:00-09:10| Jogging| Johnny| Joan|
+----------+-----------+------------+-----------+------------+

---

SO following tomhath's advice, the query looks like this:

SELECT
timetable.intWeekday,
CONCAT(time_format('%H:%i',timeTimiStart),'-',time_format('%H:%i',timeTimiEnd)) as timerange,
classes_info.strClassname,
in1.strNickname as instr1
in2.strNickname as instr2
FROM
timetable
LEFT JOIN
classes_info ON
timetable.intClass = classes_info.id
LEFT JOIN
instructors in1 ON
classes_info.intInstructor1 = instructors.id
LEFT JOIN
instructors in2 ON
classes_info.intInstructor2 = instructors.id

.... just hope someone else can learn from this.