Many to many query

Question

I have two tables products and sections in a many to many relationship and a join table products_sections. A product can be in one or more sections (new, car, airplane, old).

Products
id    name
-----------------
1     something
2     something_else
3     other_thing


Sections
id    name
-----------------
1     new
2     car

Products_sections
product_id     section_id
--------------------------
1              1
1              2
2              1
3              2

I want to extract all products that are both in the new and the car sections. In this example result returned should be product 1. What is the correct mysql query to obtain this?

Answer 1

SELECT product_id, count(*) AS TotalSection
FROM Products_sections
GROUP BY product_id
WHERE section_id IN (1,2)
HAVING TotalSection = 2;

See if this works in mysql.

Answer 2

The query below is a little unwieldy, but it should answer your question:

select products.id
from products
where products.id in 
  (
    select products_sections.product_id
    from products_sections
    where products_sections.section_id=1
  )
  and products.id in
  (
    select products_sections.product_id
    from products_sections
    where products_sections.section_id=2
  )

Answer 3

The way I always do these is this:

Start at what you're trying to get (products), and then go through your lookup table (products_sections) to what you're trying to filter by (sections). This way, you can have it in plain view what you're looking for, and you never have to memorize surrogate keys (which are a great thing to have, not to memorize).

select distinct
    p.name
from
    products p
    inner join products_sections ps on
        p.product_id = ps.product_id
    inner join sections s1 on
        ps.section_id = s1.section_id
    inner join sections s2 on
        ps.section_id = s2.section_id
where
    s1.name = 'new'
    and s2.name = 'car'

Voila. Three inner joins, and you have a nice, clear, concise query that is obvious what it's bringing back. Hope this helps!

Answer 4

Self-join on two subsets of join table and then selecting unique product ids.

SELECT DISTINCT car.product_id 
FROM ( SELECT product_id 
         FROM Product_sections 
        WHERE section_id = 2
     ) car JOIN 
     ( SELECT product_id 
         FROM Product_sections 
        WHERE section_id = 1
     ) neww 
     ON (car.product_id = neww.product_id)

This query is a variation of more general solution:

SELECT DISTINCT car.product_id
  FROM product_sections car join 
       product_sections neww ON (car.product_id = neww.product_id AND 
                                 car.section_id = 2 AND 
            neww.section_id = 2)

---

Less efficient but more straight forward solution is:

SELECT p.name FROM Products p WHERE
 EXISTS (SELECT 'found car' 
           FROM Products_sections ps 
       WHERE ps.product_id = p.id AND ps.section_id = 2)
 AND
 EXISTS (SELECT 'found new' 
           FROM products_sections ps 
       WHERE ps.product_id = p.id AND ps.section_id = 1)

----------------

I manipulated with ids for clarity. If necessary replace expressions section_id = 2 and section_id = 1 with

section_id = (SELECT s.id FROM Sections s WHERE s.name = 'car')

section_id = (SELECT s.id FROM Sections s WHERE s.name = 'new')

Also, you can select product names by plugging in any of the queries above like this:

SELECT Products.name FROM Products 
 WHERE EXISTS (
         SELECT 'found product'
           FROM product_sections car join 
                product_sections neww ON (car.product_id = neww.product_id AND 
                                          car.section_id = 2 AND 
                                          neww.section_id = 2)
          WHERE car.product_id = Products.id
       )

Answer 5

SELECT Products.name
FROM Products
WHERE NOT EXISTS (
  SELECT id
  FROM Sections
  WHERE name IN ('new','car')
  AND NOT EXISTS (
    SELECT *
    FROM Products_sections
    WHERE Products_sections.section_id = Sections.id
    AND Products_sections.product_id = Products.id
  )
)

In other words, select those products for which none of the desired Section.id values is missing from the Products_sections table for that product.

Answer andho's comment:

You can put

  NOT EXISTS (<select query>)

into a WHERE clause like any other predicate. It will evaluate to TRUE if there are no rows in the result set described by <select query>.

Stepwise, here's how to get to this query as an answer:

Step 1. The requirement is to identify all products that are "in both the 'new' and 'car' sections".

Step 2. A product is in both the 'new' and 'car' sections if both the 'new' and 'car' sections contain the product. Equivalently, a product is in both the 'new' and 'car' sections if neither of those sections fails to contain the product. (Note the double negative: neither fails to contain.) Restated again, we want all the products for which there is no required section failing to contain the product.

The required sections are these:

SELECT id
FROM Sections
WHERE name IN ('new','car')

Therefore, the desired products are these:

SELECT Products.name
FROM Products
WHERE NOT EXISTS ( -- there does not exist
  SELECT id  -- a section
  FROM Sections
  WHERE name IN ('new','car') -- that is required
   AND (the section identified by Sections.id fails to contain the product identified by Products.id)
)

Step 3. A given section (such as 'new' or 'car') does contain a particular product if there's a row in Products_sections for the given section and particular product. So a given section fails to contain a particular product if there is no such row in Products_sections.

Step 4. If the query below does contain a row, the section_id section does contain the product_id product:

SELECT *
FROM Products_sections
WHERE Products_sections.section_id = Sections.id
AND Products_sections.product_id = Products_id

So the section_id section fails to contain the product (and that's what we need to express) if the query above does not produce a row in its result, or if NOT EXISTS ().

Seems complicated, but once you get it in your head, it sticks: Are all required items present? Yes, so long as there does not exist a required item that is not present.

Answer 6

SELECT p.*
FROM   Products p
       INNER JOIN (SELECT   ps.product_id
                   FROM     Products_sections ps
                            INNER JOIN Sections s
                              ON s.id = ps.section_id
                   WHERE    s.name IN ("new","car")
                   GROUP BY ps.product_id
                   HAVING   Count(ps.product_id) = 2) pp
         ON p.id = pp.product_id

Answer 7

This query will get you the result without having to add more inner joins when you need to search more sections. What will change here are:

1. values inside the IN () paranthesis

2. The value in the where clause for count which should be replaced with the number of sections you are searching

   SELECT id, name FROM
   (
   SELECT
   products.id,
   products.name,
   sections.name AS section_name,
   COUNT(*) AS count FROM products
   INNER JOIN products_sections
   ON products_sections.product_id=products.id
   INNER JOIN sections
   ON sections.id=products_sections.section_id
   WHERE sections.name IN ('car', 'new')
   GROUP BY products.id
   ) AS P
   WHERE count = 2

Answer 8

select
`p`.`id`,
`p`.`name`
from `Sections` as `s`
join `Products_sections` as `ps` on `ps`.`section_id` = `s`.`id`
join `Products` as `p` on `p`.`id` = `ps`.`product_id`
where `s`.`id` in ( 1,2 )
having count( distinct `s`.`name` = 2 )

will return...

id    name
-----------------
1     something

Is that what you were looking for?