Double results when select SQL query [duplicate]

Question

I've tried to fix that by my self but I couldn't find any solution so maybe you can help. I have following query:

select users.name,users.userid,position,department.name,num,phone.pname,objpict.fname from users inner join opict on opict.ownerid=users.id inner join department on department.id=users.deptid inner join phone on phone.ownerid=users.id where quitted=0 and tag='web' and opict.del=0 and phone.del=0 and phone.ownertype=5

It's working great but the problem is that some users have 2 or 3 diffrent nummbers so select resoult will be:

Name UserID Number ------ ------ -------- David Test 12345678 David Test 11111111

So the problem is that output will be double. How can I fix that it will be formatted like this:

Name UserID Number ------ ------ -------- David Test 12345678 Test 11111111

Do this in your application code. SQL is not suitable for doing such manipulations. — Gurwinder Singh
– Gurwinder Singh, Commented Feb 22, 2017 at 9:06
You would better to take another approach in order to have 1 row per user and 1 column per phone number instead of several rows per users. This can work only if you have a max and limited number of phones numbers (2-3 sounds good). Example here — Thomas G
– Thomas G, Commented Feb 22, 2017 at 9:10

Radu Gheorghiu · Accepted Answer · 2017-02-22 09:19:02Z

If you don't want to display duplicate values you can make use of ROW_NUMBER() along with a CASE statement:

select case when row_number() over (partition by users.userId order by users.userId) = 1 then users.name else NULL end as userName , users.userid , position , department.name , num , phone.pname , objpict.fname from users inner join opict on opict.ownerid = users.id inner join department on department.id = users.deptid inner join phone on phone.ownerid = users.id where quitted = 0 and tag = 'web' and opict.del = 0 and phone.del = 0 and phone.ownertype = 5

If you don't want NULL to be displayed and want a "white space" you can change the second branch of the CASE statement to else ''.

Thanks:) I had to add more "case" but it's working:) Thanks all for your help.

Collectives™ on Stack Overflow

Double results when select SQL query [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related