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