CODEWITHSUNDEEP
mysql
qwertyg
qwertyg

Reputation: 137

MYSQL filter results after inner join and where

I have a mysql query via inner join as:

SELECT * FROM oc_product
INNER JOIN oc_product_to_category ON oc_product.product_id = oc_product_to_category.product_id 
WHERE oc_product_to_category.category_id = 100

so far ok, it returns the rows from table oc_product that correspond to oc_product_to_category.category_id = 100

I want to select from these results the common rows from table oc_product where oc_product_to_category.category_id = 101 or oc_product_to_category.category_id = 102 etc...

How can I accomplish this?

I tried via having as:

SELECT * FROM oc_product
INNER JOIN oc_product_to_category ON oc_product.product_id = oc_product_to_category.product_id 
WHERE oc_product_to_category.category_id = 101 OR oc_product_to_category.category_id = 102
having (oc_product_to_category.category_id = 301)

but with wrong results.

Can someone point me to the right direction (logic) ?

Upvotes: 0

Views: 89

Answers (1)

DRapp
DRapp

Reputation: 48169

Your join by the common product id is one thing, let that be just the direct condition. For including multiple categories, you can just use the "IN" clause and provide a list of the different categories you are interested in.

SELECT 
      * 
   FROM 
      oc_product p
         INNER JOIN oc_product_to_category pc
            ON p.product_id = pc.product_id 
            AND pc.category_id IN ( 101, 102, 301 )

Now, its unclear in your question, but if you were trying to imply that you are looking for a product that is in category A, but is ALSO associated with category B and wanted to make sure that BOTH categories were covered. For example with computers. You sell computers. Someone is interested in laptops (category A), but also an i7 processor (Category B) and is 15 inch screen (category C). You want to make sure that all 3 categories of interest are within the results. You don't want a 13 inch laptop, nor an i7 desktop/tower machine. If this is more the scenario, you would want to do an aggregate count on the 3 categories you want. In this case, I would do a pre-query aggregate from the product/category table that MEETS the requirements of all 3, THEN get the products that DID qualify. Something like

select
      p.*
   from
      ( select
              -- I only care about the final product ID that qualifies
              pc.product_id
           from
              oc_product_to_category pc
           where
              -- the product category must be of these 3 possible choices
              pc.category_id in ( 101, 102, 301 )
           group by
              pc.product_id
           having
                  -- first SUM() qualifies that the 301 MUST BE included
                  -- hence its = 1 value.
                  sum( case when pc.category_id = 301 
                            then 1 else 0 end ) = 1
              AND 
                  -- AND the sum() of the others is GREATER than 0.
                  -- so either OR both would qualify the product
                  sum( case when pc.category_id in ( 101, 102 )
                            then 1 else 0 end ) > 0  ) JustQualified
         -- NOW, joining to the products based on only those that qualified above
         JOIN oc_product p
            on JustQualified.product_id = p.product_id

CORRELATED UPDATE

To perform a correlated updated query against the OC_Product table which I have as alias "p" in the above query, it is only a slight change in syntax

update oc_product p
   JOIN ( select
                  -- I only care about the final product ID that qualifies
                  pc.product_id
               from
                  oc_product_to_category pc
               where
                  -- the product category must be of these 3 possible choices
                  pc.category_id in ( 101, 102, 301 )
               group by
                  pc.product_id
               having
                      -- first SUM() qualifies that the 301 MUST BE included
                      -- hence its = 1 value.
                      sum( case when pc.category_id = 301 
                                then 1 else 0 end ) = 1
                  AND 
                      -- AND the sum() of the others is GREATER than 0.
                      -- so either OR both would qualify the product
                      sum( case when pc.category_id in ( 101, 102 )
                                then 1 else 0 end ) > 0  ) JustQualified
             -- NOW, joining to the products based on only those that qualified above
      on JustQualified.product_id = p.product_id
   set p.myColumn = 22

Upvotes: 1

Related Questions