CODEWITHSUNDEEP
mysqlsql
User314159
User314159

Reputation: 8065

Left Outer Join with condition on the joinee

Assume I have the following schema

users
+-------+------------+
| Field | Type       |
+-------+------------+
| id    | integer    |
+-------+------------+


posts
+---------+-------------+
| Field   | Type        |
+---------+-------------+
| id      | integer     | 
| user_id | integer     |
| topic   | varchar(255)|
+---------+-------------+

How do I find all the users who have never created a post about Cats? I tried this query but it doesn't work.

SELECT    users.* 
FROM      users 
LEFT JOIN posts 
ON        users.id = posts.user_id 
WHERE     posts.user_id IS NULL 
AND       posts.topic LIKE 'Cats'

This returns an empty set even when there are users who have never posted about "Cats".

Upvotes: 0

Views: 39

Answers (4)

Mahesh Patel
Mahesh Patel

Reputation: 164

This will solve your problem, Have a try and let me know if helps

SELECT * FROM `users` INNER JOIN posts ON users.id=posts.user_id WHERE users.id NOT IN(SELECT users.id FROM `users` LEFT JOIN posts ON users.id=posts.user_id WHERE posts.topic LIKE '%cats%')

Upvotes: 3

Willem Renzema
Willem Renzema

Reputation: 5187

You need to add the LIKE condition to the on clause.

SELECT    users.* 
FROM      users 
LEFT JOIN posts 
ON        users.id = posts.user_id  
AND       posts.topic LIKE 'Cats'
WHERE     posts.user_id IS NULL

Most of the other answers make the mistake of using NOT LIKE in the WHERE clause, but that will return users who have any posts without cats. So, if a user posted about cats, but also posted about something not involving cats, they would incorrectly be returned in those queries.

Upvotes: 0

Manish Khurana
Manish Khurana

Reputation: 9

this might help: SELECT users.* FROM posts LEFT JOIN users ON posts.user_id = users.id WHERE posts.topic NOT LIKE %Cats% ;

Upvotes: 0

Khunti Haresh
Khunti Haresh

Reputation: 78

Try to this

SELECT    users.* 
FROM      users 
LEFT JOIN posts 
ON        users.id = posts.user_id 
WHERE     posts.topic NOT LIKE '% Cats %'

Upvotes: 2

Related Questions