CODEWITHSUNDEEP
mysqlsqlselectjoin
Ajouve
Ajouve

Reputation: 10049

Sub-query and join

I'm trying to launch a sub select with a join but I have a problem on the return

SELECT * FROM user, follow
WHERE user.id IN ( SELECT follow FROM referent_follow WHERE referent=3 )

In the above case the return is good but I have in addition all my table follow that I didn't want

SELECT * FROM user, follow
WHERE follow.user_id IN ( SELECT follow FROM referent_follow WHERE referent=3 )

In the above case the return is good but I have in addition all my table user that I didn't want

Query that works less badly is the first one but I have content of follow in my result too.

Upvotes: 1

Views: 51

Answers (2)

Bernhard Barker
Bernhard Barker

Reputation: 55649

FROM A,B means a cross-join of A and B. So every row in A is matched to every row B
(so totalRows = rowCount(A)*rowCount(B)).

Using an (INNER) JOIN should work:

SELECT *
FROM user
JOIN follow ON user.id = follow.user_id
  AND user.id IN ( SELECT follow FROM referent_follow WHERE referent=3 )

Using JOIN rather than IN would be advised, since it generally results in better performance:

SELECT user.*, follow.*
FROM user
JOIN follow ON user.id = follow.user_id
JOIN referent_follow ON user.id = referent_follow.follow
WHERE referent=3

You may also need a DISTINCT as JW suggested.

Upvotes: 1

John Woo
John Woo

Reputation: 263893

Your current query result a cartesian product from both tables: user and follow. You need to supply a condition that will join and filter out connected records.

SELECT  DISTINCT a.*, b.* 
FROM    user a
        INNER JOIN follow b
            ON a.ID = b.user_id 
        INNER JOIN referent_follow c
            ON a.ID = c.follow
WHERE   c.referent = 3

Upvotes: 1

Related Questions