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

hello

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.