SQL Select rows using LEFT JOIN - where count is 0 matches

Question

SQL Select rows using LEFT JOIN - where count is 0 matches

I need to get rows from one SQL table when there are zero matches for a specific condition in the second table. I am using SQL SELECT with LEFT JOIN. How can I create a condition with GROUP BY and COUNT to get related rows with 0 matches for a specific value ?

0

Report

14 May 2021 at 05:50 PM

Stor

sql

1 answer

OK

Tim · Answer 1

Hi,

To get only rows from one table with zero matches for a specific condition in the second table, you can use a subquery with SELECT and COUNT(), without using of LEFT JOIN.

For example, the following solution will get categories with zero related items:

SELECT c.category_name FROM item_categories AS c WHERE 'zero_matches' = (SELECT IF(COUNT(*) = 0, 'zero_matches', 'matches_found') FROM items AS i WHERE c.id_category = i.id_category) ORDER BY c.category_name;
You can also add GROUP BY into SQL if needed.

SQL Select rows using LEFT JOIN - where count is 0 matches

sql

Error: Illegal parameter data types INT and row for operation '='

Is faster one big SQL query or multiple small SQL queries ?

Fix error: mysqli_select_db / query expects 2 arguments, 1 given

Copy and update rows in the same table with autoincrement - SQL

Fix SQL error Index for table 'user_db' is corrupted, try to repair it

MySQL error: Unknown table engine 'InnoDB' - how to fix ?

Insert or update current date in DATETIME format - SQL