how to select rows which are mapped to a single record in a one to many relationship? 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) &gt; 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) &gt; 1