O'Reilly Forums: Help With A Join - O'Reilly Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Help With A Join

#1 User is offline   vbelenky 

  • New Member
  • Pip
  • Group: Members
  • Posts: 1
  • Joined: 07-June 11

Posted 07 June 2011 - 05:25 AM

Hello,

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:

name,lan,fax
Al,718,683

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.
0

#2 User is offline   tonyvh37 

  • Advanced Member
  • PipPipPipPipPipPipPipPip
  • Group: Members
  • Posts: 340
  • Joined: 20-May 06
  • 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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users