CODEWITHSUNDEEP
sql-serverleft-join
Rick
Rick

Reputation: 1559

Left join producing wrong data

I am working on joinin gtwo tables: Contacts, contacttype. Contacts table contain contact details and contacttype table contains Contacttype details.

Below is Contact table:

enter image description here

Contact type table:

enter image description here

Desired output for one client:

enter image description here

The left join I use in my query is producing wrong results. I have added my rexter link here. Any help will be greatly appreciated!

Rexter link: http://rextester.com/live/QGMAD33217

***New Rexter link(W/o live edit: http://rextester.com/KSP51539

Query:

create table contacttype(ctype_id int,ctype varchar(20))
insert contacttype values
( 12    ,'Ctype1'),
( 13    ,'Ctype2'),
( 14    ,'Ctype3')

create table contacts(cid int,ctype_id int,name varchar(20), phone varchar(15))
insert contacts values
(1001,  12  ,'Tim', 1234567890),
(1001,  13  ,'Joe', 9874563210),
(1001,  14  ,'Jack',    6547893214),
(1002,  12  ,'Jane',    6547896125),
(1002,  13  ,'Smith',   null),
(1002,  14  ,'Jill',    9878445623 )

select c.cid,
       max(case when ct.ctype = 'Ctype1' then c.name end) as [ctype1_name],
       c1.phone,
       max(case when ct.ctype = 'Ctype2' then c.name end) as [ctype2_name],
       c2.phone,
       max(case when ct.ctype = 'Ctype3' then c.name end) as [ctype3_name],
       c3.phone
from contacts c
join contacttype ct on c.ctype_id = ct.ctype_id
left join contacts c1 on c1.ctype_id = ct.ctype_id and ct.ctype = 'Ctype1'
left join contacts c2 on c2.ctype_id = ct.ctype_id and ct.ctype = 'Ctype2'
left join contacts c3 on c3.ctype_id = ct.ctype_id and ct.ctype = 'Ctype3'
group by c.cid,c1.phone,c2.phone,c3.phone

Upvotes: 0

Views: 58

Answers (2)

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

Alternatively, you can use pivot relation operator if values of ctype_id are predefined

declare @contacttype table (ctype_id int,ctype varchar(20))
insert @contacttype values
( 12    ,'Ctype1'),
( 13    ,'Ctype2'),
( 14    ,'Ctype3');

declare @contacts table (cid int,ctype_id int,name varchar(20), phone varchar(15))
insert @contacts values
(1001,  12  ,'Tim', 1234567890),
(1001,  13  ,'Joe', 9874563210),
(1001,  14  ,'Jack',    6547893214),
(1002,  12  ,'Jane',    6547896125),
(1002,  13  ,'Smith',   null),
(1002,  14  ,'Jill',    9878445623 );

with A as (
    select 
        cid, [12] as ctype1_name, [13] as ctype2_name, [14] as ctype3_name
    from
        (select cid, ctype_id, name from @contacts) p pivot (max(name) for ctype_id in ([12],[13],[14])) as pvt
), B as (
    select 
        cid, [12] as phone1, [13] as phone2, [14] as phone3
    from
        (select cid, ctype_id, phone from @contacts) p pivot (max(phone) for ctype_id in ([12],[13],[14])) as pvt
)
select
    A.cid, A.ctype1_name, B.phone1, A.ctype2_name, B.phone2, A.ctype3_name, B.phone3
from
    A inner join B on (A.cid = B.cid)

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Now that you have a stable link it is easy to see what went wrong. You have conditional aggregation but it wasn't quite the way you want it. This should produce the output you are looking for.

select c.cid
    , ctype1_name = max(case when ct.ctype = 'Ctype1' then c.name end) 
    , phone1 = max(case when ct.ctype = 'Ctype1' then c.phone end) 
    , ctype2_name = max(case when ct.ctype = 'Ctype2' then c.name end) 
    , phone2 = max(case when ct.ctype = 'Ctype2' then c.phone end) 
    , ctype3_name = max(case when ct.ctype = 'Ctype3' then c.name end) 
    , phone3 = max(case when ct.ctype = 'Ctype3' then c.phone end) 
from contacts c
join contacttype ct on c.ctype_id = ct.ctype_id
group by c.cid

Upvotes: 2

Related Questions