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


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

tesuji is right but you should not group by the positionid also.

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