1

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
McNets
  • 10,352
  • 3
  • 32
  • 61
DJ_TecRoot
  • 93
  • 8
  • 7
    Do this in your application code. SQL is not suitable for doing such manipulations. – Gurwinder Singh Feb 22 '17 at 09: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](http://stackoverflow.com/questions/10196237/sql-select-phone-numbers-from-a-many-to-many-table-with-different-types-mobile) – Thomas G Feb 22 '17 at 09:10

1 Answers1

3

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

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107