Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Left join producing wrong result
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
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Combined Where and having
Fff
Test
Solution 4
sql server dynamic 12 month time series and pivot with month year column names
Sql varchar to date
SQL flight 2
Shalvika's Query
testing
MindBoxTest(A.M.)
Please log in to post a comment.