Jump to content


Help With A Join

  • Please log in to reply
1 reply to this topic

#1 vbelenky


    New Member

  • Members
  • Pip
  • 1 posts

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.

#2 tonyvh37


    Advanced Member

  • Members
  • PipPipPipPipPipPipPipPip
  • 340 posts
  • Gender:Male
  • Location:Indiana
  • Interests:PC stuff - Gaming to Programming

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