I have two tables, consumer and user. Consumer table has a foreign key for user table,say user_id mapped to user id in user table. There are some rows in consumer table which have one to one mapping with user table while some rows are mapped to same user id in user table. I want to select only those rows from consumer table where more than one record is mapped to a same user id.


Unless you are needing to return data that is in the user table you don't really need to involve it in your query. In that case you could implement a query such as

SELECT c.user_ID, c.col1, c.col2
FROM consumer c
GROUP BY c.user_ID, c.col1, c.col2
HAVING COUNT(c.user_ID) > 1

Were you to need a result set including results from both tables I would use a query similar to the one Marco suggests.

SELECT c.user_ID, c.col1, c.col2, h.col1, h.col2
FROM consumer c
INNER JOIN user_table u
      ON c.user_ID = u.user_ID
GROUP BY c.user_ID, c.col1, c.col2, h.col1, h.col2
HAVING COUNT(c.user_ID) > 1

Try something like:

select   user.userid, count(*)
from     user inner join consumer on user.user_id=consumer.user_id
group by user.user_id
having   count(*)=1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.