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.