mysql checking if product ID also exist in other table

Question

I have not much experience with JOINS and the result I get with query below isn't correct. I have a table called products and want to check if there are records in the table product_links. I only want to get a list of items that doesn't have rows in product_links.

When I run the below query, I only get one line. Anybody suggestions? Google couldn't help me or I'm searching with the wrong keywords.

SELECT a.id, a.SKU, a.title, 
(SELECT COUNT(b.id) AS amount FROM product_links WHERE b.product=a.id) AS amount
FROM products AS a
LEFT JOIN product_links AS b ON b.product=a.id

A hint: take a habit of typing the meaningful part of the question in your browser. For example, typing `mysql get a list of items that doesn't have rows in other table` will get you an answer in an instant. — Your Common Sense, Nov 14 '20 at 10:21

score 0 · Answer 1 · answered Nov 14 '20 at 10:22

0

I would recommend not exists:

select p.*
from products p
where not exists (select 1 from product_links pl where pl.product_id = p.id)

answered Nov 14 '20 at 10:22

GMB

216,147
25
84
135

score 0 · Answer 2 · edited Apr 10 '22 at 08:11

0

From your question i understand you need info of products which doesnt have any links.

Below is the query for that

SELECT * FROM products 
WHERE id NOT IN (SELECT id FROM product_links);

edited Apr 10 '22 at 08:11

Dexter

7,911
4
41
40

answered Nov 14 '20 at 10:24

RAJESH R K

16

mysql checking if product ID also exist in other table

2 Answers2