CODEWITHSUNDEEP
mysqlsqljoin
Waaghals
Waaghals

Reputation: 2619

Get all records where related records does not have any other relationships

I have a table with products which has many variants. The variant has zero to many images.

+---------+
| Product |
+---------+
    1|    
     |    
     |1..n   
+---------+
| Variant |
+---------+
    1|    
     |    
     |0..n   
+---------+
|  Image  |
+---------+

I need to update all products of which none of the variants have any images. I can find all products which some variants that have no images using the following query:

SELECT
   DISTINCT `p`.*
FROM `sylius_product` AS `p`
    JOIN `sylius_product_variant` AS `v` ON `v`.`product_id` = `p`.`id`
    LEFT JOIN `sylius_product_variant_image` AS `i` ON `i`.`variant_id` = `v`.`id`
WHERE `i`.`id` IS NULL

But if I'm not mistaken, a product with two variant where one has images and one has not will be included in the result set.

How can I construct my joins / where so that I can retrieve only product where all variants do not have any images?

Upvotes: 0

Views: 44

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can get all products whose all variants don't have images by doing 

SELECT  `p`.`id`
FROM `sylius_product` p
LEFT JOIN (
SELECT  DISTINCT `v`.`product_id`
FROM  `sylius_product_variant` AS `v` 
JOIN `sylius_product_variant_image` AS `i` ON `i`.`variant_id` = `v`.`id`
) p1
ON `p`.`id` = `p1`.`product_id`
WHERE `p1`.`product_id` IS NULL

Subquery in join part will get products whose variants have images then in main query we can eliminate these products by using left join

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271031

You can do this by joining and aggregating:

select p.*
from sylius_product p join
     sylius_product_variant v
     on v.product_id = p.id left join
     sylius_product_variant_image i
     on i.variant_id = v.id
group by p.id
having max(i.variant_id) is null;

If none of the variants have images, then there are no images at all on the product. The having condition can also be written as:

having count(i.variant_id) = 0

Upvotes: 1

Related Questions