CODEWITHSUNDEEP
mysqlsql
Avdept
Avdept

Reputation: 2289

retrieve only records that has join records

I have following sql query, which retrieves me joined records. However I want to get places which doesnt have joined records, see below:

SELECT `places`.* FROM `places` INNER JOIN `quotes` ON `places`.`id` = `quotes`.`place_id` 
WHERE `quotes`.`user_id` = 1 AND

Here comes part I don't know how to write, but I want only to get places where count of quotes = 0 like: 

"quotes.count == 0"

How to add another clause to this sql query, to match my requests?

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

You want an outer join, presumably a left outer join:

SELECT `places`.*
FROM `places` LEFT JOIN
     `quotes`
     ON `places`.`id` = `quotes`.`place_id` AND
        `quotes`.`user_id` = 1
WHERE quotes.place_id is null;

Note that the condition in the where clause has been moved to the on clause. When there is no match, the columns from quotes are filled with NULLs -- which would cause a where condition to fail.

Upvotes: 2

Related Questions