How to translate this query to join?

Question

My MariaDB doesn't support IN and I cannot seem to write a proper query that uses JOIN and gives the same result. Here's the query with IN

SELECT * FROM topics WHERE topic_id IN 
( SELECT DISTINCT(topic) FROM answers 
ORDER BY date LIMIT 500 )

How would that query translate to something that doesn't use IN but JOIN (prefrably)?

Answer 1

You can use a correlated sub-query.

SELECT * 
FROM topics 
INNER JOIN
(SELECT DISTINCT(topic) as topic FROM answers LIMIT 500) x on x.topic = topic_id  

Answer 2

try this one with subquery. As you wrote your code I suppose you prefer an INNER JOIN.

I just got rid of ORDER BY, because it only makes sense on the outermost query. And mostly because if you select distinct you can't order by without duplicate values... try to explain me better your ordering, because we may need another nested select.

Answer this: "2 equal topic FROM answers with different date, is it possible? If so which date to consider?"

SELECT A.* 
FROM topics A INNER JOIN
( 
SELECT DISTINCT(topic) AS topic_B FROM answers 
LIMIT 500 
) B
ON (A.topics_id = B.topic_b)

Answer 3

Aliasing your table helps for keeping joins straight in your head. (The t and a as seen below) Inner join only returns where results match, similar function to where in.

SELECT T.* FROM topics t
INNER JOIN
(
 SELECT DISTINCT(topic) 
 FROM answers
 ORDER BY date LIMIT 500
) a
ON t.topic_id = a.topic