Get records from table 1 which are not presented in table 2

Question

What is wrong with this query:

SELECT `product`.`id`, `title` AS `text` FROM `product` 
LEFT JOIN `productlang` ON product.id=productlang.product_id 
LEFT JOIN `new_product` ON product.id=new_product.product_id 
WHERE (`product`.`id` <> `new_product`.`product_id`) 
AND (`title` LIKE '%nik%') 
AND (`language`='bg') 
LIMIT 20

The purpose is to get all products from product table whose id doesn't exist in new_product (product_id is the related column).I tought WHERE (product.id <> new_product.product_id) part should do the trick. Where is my bad ? Thank you!

title and language are in which table ? – Madhur Bhaiya Nov 05 '18 at 20:00 — Madhur Bhaiya, Nov 05 '18 at 20:00

Madhur Bhaiya · Accepted Answer · 2018-11-05T20:08:27.483

1

Whenever using Left Join, please ensure that your Where conditions on the right-side table of a Left Join are specified in the On clause. Otherwise, this would limit your result-set.
To check if there is no matching entry in the right-side table, we can use IS NULL.
Please use proper aliasing when dealing with multi-table queries, for readability and clarity purposes.

Try the following:

SELECT p.id, 
       pl.title AS `text` 
FROM product AS p 
LEFT JOIN productlang AS pl  
  ON p.id = pl.product_id AND 
     pl.title LIKE '%nik%' AND 
     pl.language = 'bg' 
LEFT JOIN new_product AS np 
  ON p .id = np.product_id 
WHERE np.product_id IS NULL 
LIMIT 20

edited Nov 05 '18 at 20:08

answered Nov 05 '18 at 20:01

Madhur Bhaiya

28,155
10
49
57

It can be `NULL`. The record is there or not. – Toma Tomov Nov 05 '18 at 20:03
After the edit now it did the trick. Thank you very much! – Toma Tomov Nov 05 '18 at 20:04
@TomaTomov happy to help. do check the added explanation. – Madhur Bhaiya Nov 05 '18 at 20:07
Checking it right now :D Thank you for it ! Appreciate that type of answers :) – Toma Tomov Nov 05 '18 at 20:07

Get records from table 1 which are not presented in table 2

1 Answers1