CODEWITHSUNDEEP
phpmysqlsqlselect
Nicolas.
Nicolas.

Reputation: 453

MySQL - INNER JOIN query with many to many relation

I'm trying to figure out to work with JOIN, however I'm stuck somewhere.

I have two tables:

Post (postid,title) with values (1,'a'), (2,'b'),(3,'c'),(4,'d'),...
Filter (postid,reader) values (1,3),(1,2),(1,6),(1,10),(2,1),...

And I'm trying this query:

SELECT post.title
FROM post
INNER JOIN filter ON filter.reader ='2'
LIMIT 0 , 10

What I want are all the titles from table post where the id (in this case 2) matches the reader in table filter.

The query works but I'm getting duplicates, so I'm missing a crucial point somewhere ?

Upvotes: 3

Views: 1788

Answers (3)

John Woo
John Woo

Reputation: 263833

The reason why you are getting multiple result is because your query produces cartesian product from both tables due because you didn't specify on how the tables should be joined.

SELECT post.title
FROM   post
       INNER JOIN filter 
           ON filter.postID = post.postID // tells how are tables be joined
WHERE  filter.reader = '2'
LIMIT 0 , 10

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 6

Roger
Roger

Reputation: 1693

You can also do it like this

"select post.*,filter.* from post, filter where filter.postID=post.postID  and filter.reader='2' limit 0,10"

Hope this helps

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191789

SELECT post.title
FROM post
JOIN filter USING (postid)
WHERE filter.reader ='2'
LIMIT 10

You were joining on the condition that filter.reader = '2', which is equivalent to having it in the WHERE clause. That means you were performing a cross join (cross product) of all post and filter rows and then filtering those results on filter = '2', which would result in many posts with a mismatched post ID. You need to add the additional condition that the Post and Filter postids match.

Upvotes: 1

Related Questions