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