CODEWITHSUNDEEP
mysql
Conner Burnett
Conner Burnett

Reputation: 512

Mysql filter result based off of another table

I have some tables and am wanting to remove the ones that exist in another table from the result.

Here is the first call so you can see what the table looks like that I am working with:

    SELECT a.id_product, b.id_product_attribute 
    FROM ps_product_lang a 
    LEFT JOIN ps_product_attribute b 
    ON (a.id_product = b.id_product)

Table:

id_product | id_product_attribute
   25              null
   26              null
   27              192
   27              193
   27              194
   27              195

In my other table I have this:

SELECT * FROM eds_combinable_products

id_product  |  id_product_attribute
   25                 null
   27                 194

I tried to filter my result by making this call, but have had no luck with the products that do not have a product attribute. It will remove the ones with an attribute just fine.

SELECT a.id_product, b.id_product_attribute 
FROM ps_product_lang a 
LEFT JOIN ps_product_attribute b 
ON (a.id_product = b.id_product)
WHERE NOT EXISTS(
      SELECT c.id_product, c.id_product_attribute 
      FROM eds_combinable_products as c
      WHERE a.id_product = c.id_product
                    AND
            b.id_product_attribute = c.id_product_attribute
      )

Upvotes: 0

Views: 37

Answers (2)

paul
paul

Reputation: 22001

Or, perhaps more simply:

SELECT  a.id_product, 
        b.id_product_attribute 
FROM    ps_product_lang a LEFT JOIN ps_product_attribute b ON (a.id_product = b.id_product)
        LEFT JOIN eds_combinable_products as c on a.id_product = c.id_product
WHERE   c.id_product_attribute is not null

Upvotes: 1

Conner Burnett
Conner Burnett

Reputation: 512

I figured it out by adding an IF statement to the NOT EXISTS call

SELECT a.id_product, b.id_product_attribute FROM ps_product_lang a 
LEFT JOIN ps_product_attribute b ON (a.id_product = b.id_product)
WHERE NOT EXISTS(SELECT c.id_product, c.id_product_attribute FROM eds_combinable_products as c WHERE      a.id_product = c.id_product AND IF(b.id_product_attribute,b.id_product_attribute,0) = c.id_product_attribute)

Upvotes: 0

Related Questions