Consolidate data from three rows (with nulls) into one

Question

I have three tables:

Profile -ProfileID -FirstName -LastName ProfilePhoneNumber -ProfileID -PhoneNumberID PhoneNumber -PhoneNumberID -PhoneNumberTypeID -Number

ProfilePhoneNumber is a simple bridge table between Profile and PhoneNumber.

I want to query for specific phone number types and return a single row. I want to be able to accept null values because not all people will have all types of phone numbers.

Here is my current query:

SELECT p.FirstName , p.LastName , bpn.Number as BusinessPhoneNumber , mpn.Number as MobilePhoneNumber FROM Profile p LEFT JOIN ProfilePhoneNumber ppn ON p.ProfileID = ppn.ProfileID LEFT JOIN PhoneNumber bpn ON ppn.PhoneNumberID = bpn.PhoneNumberID AND bpn.PhoneNumberTypeID = '1' LEFT JOIN PhoneNumber mpn ON ppn.PhoneNumberID = mpn.PhoneNumberID AND mpn.PhoneNumberTypeID = '2' WHERE p.ProfileID = '123'

This always works, but returns three rows because Profile 123 has three phone numbers, and so the query returns a row for each phone number.

If I change it to an INNER JOIN on PhoneNumber, I can get only one row back, but only in circumstances where the Profile being queried has all of the PhoneNumberTypeID types that I am querying for.

How do I return one row that is null tolerant?

Does changing it to Select distinct solve your problem? If not, how do you want to choose which of the business or mobile phone numbers to display, when someone has more than one? — APH
– APH, Commented Jul 28, 2015 at 21:31
@APH Good Question. I'm not sure if Select distinct would work here, or if I need to cheat and do a TOP 1. I'm sure I can add more conditions to be more selective, but right now my focus is on returning a single row that is null tolerant. — Wesley
– Wesley, Commented Jul 28, 2015 at 21:34
I would suggest that all three of the LEFT JOINs be equally indented - the way they are currently indented suggests that the 2nd and 3rd are subqueries of the first - which they're not. — Hutch
– Hutch, Commented Jul 28, 2015 at 23:30

MrSimpleMind · Accepted Answer · 2015-07-28 21:41:42Z

I will update the query later, if you provide some data sample and expected output.

So far, will this help?

SELECT p.FirstName , p.LastName , Max(bpn.Number) as BusinessPhoneNumber , Max(mpn.Number) as MobilePhoneNumber FROM Profile p LEFT JOIN ProfilePhoneNumber ppn on p.ProfileID = ppn.ProfileID LEFT JOIN PhoneNumber bpn on ppn.PhoneNumberID = bpn.PhoneNumberID AND bpn.PhoneNumberTypeID = '1' LEFT JOIN PhoneNumber mpn on ppn.PhoneNumberID = mpn.PhoneNumberID AND mpn.PhoneNumberTypeID = '2' WHERE p.ProfileID = '123' group by p.FirstName, p.LastName;

Testing this in a few scenarios, but this seems to work. I'm wrapping this statement in an OUTER JOIN as it is nested in a much larger query.

Salman Arshad · Accepted Answer · 2015-07-29 17:31:26Z

If each profile has one phone per type then you can:

Use INNER JOIN queries to match profile with each phone type
Use LEFT JOIN query to match the profiles with the above

DDL:

CREATE TABLE profile (ProfileID INT NOT NULL, FirstName VARCHAR(100), LastName VARCHAR(100), PRIMARY KEY (ProfileID)); INSERT INTO profile (ProfileID, FirstName, LastName) VALUES (1, 'User', '#1'), (2, 'User', '#2'), (3, 'User', '#3'); CREATE TABLE phonenumber (PhoneNumberID INT NOT NULL, PhoneNumberTypeID INT, Number VARCHAR(100), PRIMARY KEY (PhoneNumberID)); INSERT INTO phonenumber (PhoneNumberID, PhoneNumberTypeID, Number) VALUES (1, 1, '0800-U1BUS'), (2, 1, '0800-U2BUS'), (3, 2, '0800-U2MOB'), (4, 1, '0800-U3BUS'), (5, 2, '0800-U3MOB'), (6, 3, '0800-U3ETC'); CREATE TABLE profilephonenumber (ProfileID INT NOT NULL, PhoneNumberID INT NOT NULL, PRIMARY KEY (ProfileID,PhoneNumberID)); INSERT INTO profilephonenumber (ProfileID, PhoneNumberID) VALUES (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6);

Query:

SELECT Profile.FirstName, Profile.LastName, BusPhone.Number AS BusPhoneNumber, MobPhone.Number AS MobPhoneNumber FROM profile LEFT JOIN ( SELECT ProfileID, Number FROM profilephonenumber INNER JOIN phonenumber ON profilephonenumber.PhoneNumberID = phonenumber.PhoneNumberID WHERE PhoneNumberTypeID = 1 ) AS BusPhone ON Profile.ProfileID = BusPhone.ProfileID LEFT JOIN ( SELECT ProfileID, Number FROM profilephonenumber INNER JOIN phonenumber ON profilephonenumber.PhoneNumberID = phonenumber.PhoneNumberID WHERE PhoneNumberTypeID = 2 ) AS MobPhone ON Profile.ProfileID = MobPhone.ProfileID

Output:

+-----------+----------+----------------+----------------+ | FirstName | LastName | BusPhoneNumber | MobPhoneNumber | +-----------+----------+----------------+----------------+ | User | #1 | 0800-U1BUS | NULL | | User | #2 | 0800-U2BUS | 0800-U2MOB | | User | #3 | 0800-U3BUS | 0800-U3MOB | +-----------+----------+----------------+----------------+

SQL Fiddle

HABO · Accepted Answer · 2015-07-28 22:13:01Z

-- Sample data. declare @Profile as Table ( ProfileId Int Identity, FirstName VarChar(10), LastName VarChar(10) ); insert into @Profile ( FirstName, LastName ) values ( 'Alice', 'Aardvark' ), ( 'Bob', 'Bear' ), ( 'Cindy', 'Cat' ); declare @PhoneNumber as Table ( PhoneNumberId Int Identity, PhoneNumberTypeId Int, Number VarChar(10) ); insert into @PhoneNumber ( PhoneNumberTypeId, Number ) values ( 1, '1111111111' ), ( 2, '1112221111' ), ( 1, '1113331111' ), ( 2, '2222222222' ); declare @ProfilePhoneNumber as Table ( ProfileId Int, PhoneNumberId Int ); insert into @ProfilePhoneNumber ( ProfileId, PhoneNumberId ) values ( 1, 1 ), ( 1, 2 ), ( 2, 3 ), ( 3, 4 ); -- Dump the sample data. select * from @Profile as P left outer join @ProfilePhoneNumber as PPN on PPN.ProfileId = P.ProfileId left outer join @PhoneNumber as PN on PN.PhoneNumberId = PPN.PhoneNumberId; -- Do the deed. with ExtendedPhoneNumbers as ( select ProfileId, PhoneNumberTypeId, Number from @ProfilePhoneNumber as PPN inner join @PhoneNumber as PN on PN.PhoneNumberId = PPN.PhoneNumberId ) select P.FirstName, P.LastName, EPNB.Number as BusinessPhoneNumber, EPNM.Number as MobilePhoneNumber from @Profile as P left outer join ExtendedPhoneNumbers as EPNB on P.ProfileID = EPNB.ProfileID and EPNB.PhoneNumberTypeId = 1 left outer join ExtendedPhoneNumbers as EPNM on P.ProfileID = EPNM.ProfileID and EPNM.PhoneNumberTypeId = 2 where P.ProfileId = 2; -- Comment out the WHERE clause to see all profiles.

Collectives™ on Stack Overflow

Consolidate data from three rows (with nulls) into one

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related