CODEWITHSUNDEEP
sqlsql-servert-sql
Wesley
Wesley

Reputation: 5621

Consolidate data from three rows (with nulls) into one

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?

Upvotes: 2

Views: 62

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272076

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

  1. Use INNER JOIN queries to match profile with each phone type
  2. 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

Upvotes: 2

HABO
HABO

Reputation: 15816

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

Upvotes: 0

MrSimpleMind
MrSimpleMind

Reputation: 8587

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;

Upvotes: 2

Related Questions