CODEWITHSUNDEEP
mysqljoin
user2429958
user2429958

Reputation: 27

MySQL Left Join and excluding values

UPDATE

There is a database model in sqfiddle: http://sqlfiddle.com/#!2/8dbb0/10

And I updated the question according to the annotations.

Original Post I have three tables:

posts
tags
tag_to_post

Lets asume a tag_id 1 that has been used by user 2. Now I want to show user 2 all posts, that another user has tagged with tag_id 1, but user 2 has not tagged with tag_id 1 so far.

The query: 

SELECT posts.id AS post_id, tags.id AS tag_id, tag_to_post.user_id AS 
user_tagged_post  
FROM posts  
LEFT JOIN tag_to_post ON tag_to_post.post_id = posts.id  
LEFT JOIN tags ON tags.id = tag_to_post.tag_id  
WHERE tags.id =1

Produces something like:

post_id | tags_id | user_tagged_post  
1      | 1       | 1  
1      | 1       | 2  
2      | 1       | 2  
3      | 1       | 1

So there should only be left post id 3.

First I tried with where-statement like:

WHERE tags.id = 1 AND tag_to_post.user_id != '2'

But this of course doesn't exclude post_id 1 cause it is a douplicate. I think there should be a DISTINCT or GROUPED BY before the WHERE clause, but this seems not to be allowed. So the only way is a sub-query? I didn't find a solution so far. Any ideas?

Upvotes: 0

Views: 147

Answers (2)

Sergey Onishchenko
Sergey Onishchenko

Reputation: 7851

May be you need something like this

SELECT posts.id, posts.title, tags.tag, tag_to_post.user_id
FROM posts 
INNER JOIN tag_to_post ON tag_to_post.post_id = posts.id 
INNER JOIN tags ON tags.id = tag_to_post.tag_id 
WHERE tags.id = 1 AND tag_to_post.user_id <> 2

Based on comments:

SELECT DISTINCT posts.id, posts.title, tags.tag, A.user_id
FROM posts 
INNER JOIN tag_to_post A ON A.post_id = posts.id 
INNER JOIN tags ON tags.id = A.tag_id 
WHERE tags.id = 1
AND A.post_id NOT IN (SELECT post_id FROM tag_to_post WHERE tags.id = 1 AND user_id = 2)

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

If I understand you correctly, it would seem like a straight forward LEFT JOIN;

SELECT t1.post_id, p.title, t1.tag_id, t1.user_id 
FROM tag_to_post t1
JOIN posts p ON t1.post_id = p.id
LEFT JOIN tag_to_post t2
  ON t1.tag_id  = t2.tag_id AND t1.post_id = t2.post_id AND t2.user_id = 2
WHERE t1.user_id <> 2 AND t2.user_id IS NULL

An SQLfiddle to test with.

Upvotes: 1

Related Questions