Clever Geek Handbook

Lots of requests

I have two product and section tables in many ways and a products_sections join table. A product can be in one or more sections (new, car, plane, 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 in both the new and the automotive section. In this example, the returned result should be product 1. What is the correct mysql query to get it?

+2


source to share


8 answers 


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 required Section.id values ​​are present in the Products_sections table for that product.

Answer and comment:

You can put

  NOT EXISTS (<select query>)

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

Step by step, here's how to get to that request as a response:

Step 1. The requirement is to identify all products that are "new" and "car".

Step 2. The product is in the "new" and "car" sections if the "new" and "car" sections contain a product. Equivalently, the product is in the "new" and "vehicle" sections if none of those sections contain the product. (Note that double negative: neither fails to contain.) Reiterated, we want all products for which there is no fault section to contain the product.



The required sections are as follows:

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

Therefore, the required products are:

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. In this section (for example, "new" or "car") there is one containing a specific product if there is a line in the Products_sections section for this section and for a specific product. Thus, this section does not allow you to contain a specific product if there is no such line in Products_sections.

Step 4. If the query below has found a string section section_id contains product product_id:

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

Thus section_id section does not contain the product (and what we need to express), if the above request is not creates a string in its result, or if NOT EXISTS ().

Sounds complicated, but once you get it in your head, it sticks: are all the necessary items present? Yes, if the required element does not exist, which does not exist.

+6


source


How I always do it:

Start with what you are trying to get ( products

) and then go into your lookup table ( products_sections

) to what you are trying to filter ( sections

). This way, you can keep in mind what you are looking for and you never need to memorize surrogate keys (which is very important 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 join

s, and you have a nice, easy to understand, succinct query that is obvious what it returns. Hope this helps!

+2


source 


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.

0


source


The query below is a bit cumbersome, but 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
  )
0


source


Self-join on two subsets of the join table and then select 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 on a 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 direct solution:

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 have been manipulating the 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')

In addition, you can choose product names by including any of the following queries:

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
       )
0


source 


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
0


source


This query will give you the result without having to add additional inner joins when you need to search for more partitions. What will change here:

  • inside palette IN ()

  • The value in the where clause for count to be replaced with the number of sections you are looking for

    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

0


source 


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 this what you were looking for?

0


source






More articles:


All Articles