mysql table join select

Question

I have 2 tables:

• TOPICS (id, title)
• ANSWERS (id, id_topic, id_user, answer)

...and i want to do a select to detect all the questions belonging to a user in one select.

I tried to do a join, but that doesn't work since if a user answered a topic twice, it will return 2 rows with the same topic. I tried to do a SELECT DISTINCT, but that fails also.

Answer 1

I can't quite understand your question, could you please make it a little more clearer?

Answer 2

SELECT * FROM topics
WHERE id IN (SELECT id_topic FROM answers WHERE id_user = ?);

It doesn't matter if some values are returned more than once by the subquery. Any given row in the outer query will either match or not.

Like E. F. Codd said, "if something is true, saying it twice doesn't make it more true."

Answer 3

If you are trying to see which topics a user answered, try this:

SELECT t.title, a.id_user, COUNT(a.id)
FROM topics t LEFT JOIN answers a
  ON (t.id=a.topic_id)
GROUP BY t.title, a.id_user;

The above will give you the title, user and number of answers from that user (if any). If you only want answered topics, use JOIN instead of LEFT JOIN.

Answer 4

Try:

SELECT id, topic 
  FROM topics 
 WHERE id IN (SELECT id_topic 
                FROM answers 
                WHERE id_user = <USERID>)

...or:

SELECT id, topic 
  FROM topics 
 WHERE EXISTS (SELECT * 
                 FROM answers 
                WHERE id_user = topics.id)

...and test for performance according to your indexing regimen. Recent versions of MySQL only.