Sql Select phone numbers from a many to many table with different types (mobile, home)

Question

I have a simple table schema:

Person: ID, Name PhoneNumber: ID, Type, Number #Type can be 'home' or 'mobile'. PersonPhoneNumber: ID, Person_ID, PhoneNumber_ID #A join table that connects the #person to a phone number.

As data I have:

Person: 1, "Ed" PhoneNumber: 1, "home", 1111 PhoneNumber: 2, "mobile", 2222 PersonPhoneNumber: 1, 1 /*(Person_ID)*/, 1 /*(PhoneNumber_ID*/ PersonPhoneNumber: 2, 1 /*(Person_ID)*/, 2 /*(PhoneNumber_ID*/

I want to write a view that returns:

Name |Home |Mobile ----------------------------- "Ed" 1111 2222 "Joe" 3333 4444 ... etc

Any tips on how I approach this?

Note: These tables are a snippet from a larger schema which explains why its a many to many and not more simplistic.

Is it really true that 1) there are only two types, home and mobile and 2) each person can have only one phone number of each type? — Larry Lustig
– Larry Lustig, Commented Apr 17, 2012 at 17:45
1) no, there may be many. 2) no a person may have multiple phones of a single type. The view is being created however to support a legacy scheme where a person could only have 0..1 Home PhoneNumber and 0..1 Mobile PhoneNumber — ed.
– ed., Commented Apr 17, 2012 at 22:57

Justin Pihony · Accepted Answer · 2012-04-20 14:49:31Z

You could do this in a more complicated way if you have multiple numbers or might have more than home/mobile, but for now, if you only have two numbers, then this will work:

SELECT Name, HomeNumber.Number AS Home, MobileNumber.Number AS Mobile FROM Person LEFT JOIN PersonPhoneNumber HomeMap ON Person.ID = HomeMap.Person_ID LEFT JOIN PhoneNumber HomeNumber ON HomeMap.PhoneNumber_ID = HomeNumber.ID AND HomeNumber.Type = 'home' LEFT JOIN PersonPhoneNumber MobileMap ON Person.ID = MobileMap.Person_ID LEFT JOIN PhoneNumber MobileNumber ON MobileMap.PhoneNumber_ID = MobileNumber.ID AND MobileNumber.Type = 'mobile'

BTW, if you dont want people with any numbers, then you can make the PersonPhoneNumber mapping a JOIN instead of a LEFT JOIN

I've been running this sql but I am getting back 2 rows like this: ed,1111,null then ed,null,2222. How can I merge them to one set?
@ed. I just updated my code. You need to use a new reference to the mapping table. That should work

Andomar · Accepted Answer · 2012-04-17 17:50:14Z

select p.Name , pnh.Number as Home , pnm.Number as Mobile from Person p left join PersonPhoneNumber pn on pn.Person_ID = p.ID left join PhoneNumber pnh on pnh.ID = pn.PhoneNumber_ID and phh.Type = 'home' left join PhoneNumber pnm on pnm.ID = pn.PhoneNumber_ID and pnm.Type = 'mobile'

Rohan · Accepted Answer · 2012-04-18 04:12:09Z

Hope this helps

 SELECT DISTINCT P.NAME, (SELECT DISTINCT PN1.PH_NUMBER FROM PHONE_NUMBER PN1 WHERE PN1.ID = PN.ID AND PN1.TYPE = 'Home') AS HOME, (SELECT DISTINCT PN1.PH_NUMBER FROM PHONE_NUMBER PN1 WHERE PN1.ID = PN.ID AND PN1.TYPE = 'Mobile') AS MOBILE FROM PERSON P, PHONE_NUMBER PN ORDER BY P.NAME

Kanishka · Accepted Answer · 2012-04-18 13:20:00Z

This will help you

select * from @Person p left join @PersonPhoneNumber ppn on p.ID = ppn.PersonID right join @PhoneNumber pn on ppn.PhoneNoID = pn.ID

Collectives™ on Stack Overflow

Sql Select phone numbers from a many to many table with different types (mobile, home)

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related