userid | personID | positionID

712 | 912 | 8

713 | 814 | 15

516 | 811 | 9

712 | 912 | 9

713 | 814 | 15

from the above table i have to find out userID of the person who has different multiple postion. i.e my query should return userID=712 because the user has 2 different positions.
Honestly i don't how to start
Any help will be really appreciaited

7 Years
Discussion Span
Last Post by jbisono


you may try this:

select userid, personid, positionid, count(positionid) as cntpos from yourtable
  group by userid, personid, positionid having cntpos > 1;

btw, your table isn't that well normalized.

-- tesu


thanks guys for quick reply, i know table is not normalized but can't change the table. sorry the code did not work, the code display all the userids. the sql query should display only the userid who have multiple different positions.therefore the code should display only userid 712 because it has two different positions i.e 8 and 9.


like our friend tesuji said

SELECT userid, count(positionid) AS cntpos FROM yourtable
   GROUP BY userid
   HAVING count(positionid) > 1;

that should work

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.