Jump to content
Help With A Join
1 reply to this topic
Posted 07 June 2011 - 05:25 AM
My schema is:
create table person(id int,name varchar(10));
create table person_phone(person_id int, phone_id int);
create table phone(id int, type char(3), num varchar(10));
The data is:
insert into person values(3, 'Al');
insert into person_phone values(3,4);
insert into person_phone values(3,5);
insert into phone values(4,'LAN', '718');
insert into phone values(5,'FAX', '683');
The query resultset should be:
However, it's empty. The query that I run is:
select p.name as name, l.num as lan, f.num as fax
from person as p
inner join person_phone as pp on pp.person_id = p.id
inner join phone as l on l.id = pp.phone_id and l.type='LAN'
inner join phone as f on f.id = pp.phone_id and f.type='FAX'
I wonder if anybody can explain why this happens.
Posted 13 June 2011 - 04:57 AM
It is because you have to specify your cross reference table for each record you want to add. I'm not sure I can explain exactly what is going on, but if you change your join to include the cross ref table twice it should work. Something like this:
select * from person p inner join person_phone pp on p.id = pp.person_id inner join phone ph on pp.phone_id = ph.id and ph.type = 'LAN' inner join person_phone pp2 on p.id = pp2.person_id inner join phone ph2 on pp2.phone_id = ph2.id and ph2.type = 'FAX'
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users