Remove results from SELECT by conditions on multiple columns

Question

So I have this feed of products

id man sup product
1  1   1   MacBook
2  1   2   iMac
3  2   1   Windows
4  2   2   Office

and then tables with manufacturers

id manufacturer
1  Apple
2  Microsoft

and suppliers

id supplier
1  TechData
2  Westcoast

Then, for some reasons, I don't want to show a manufacturer's products by a certain supplier, i.e.:

id man sup comment
1  2   1   TechData aren't allowed to sell Microsoft 
2  1   2   hide all Apple products from Westcoast

Is there a way, in pure SQL, to show only the products which fall through my filter, in this case MacBook and Office? I believe this isn's just a WHERE NOT (x AND y) as the result will list the remaining combinations.

Thanks a lot!

Why isn't this `WHERE NOT (x AND y)`? That seems like the logic you are describing. — Gordon Linoff, Apr 25 '17 at 00:24
Because the select generates a matrix of all combinations, with only these removed. — Josef Habr, Apr 25 '17 at 01:57

score 1 · Accepted Answer · edited May 23 '17 at 12:09

1

This is just a variation on Return row only if value doesn't exist, except you're joining on two columns.

SELECT p.product, m.manufacturer, s.supplier
FROM products AS p
JOIN manufacturers AS m ON m.id = p.man
JOIN suppliers AS s ON s.id = p.sup
LEFT JOIN filter AS f ON p.man = f.man AND p.sup = f.sup
WHERE f.id IS NULL

DEMO

edited May 23 '17 at 12:09

Community

1
1

answered Apr 25 '17 at 00:32

Barmar

741,623
53
500
612

Avidos · Answer 2 · 2017-04-25T01:00:12.727

You can try this, mate:

First, create the temporary/real container for your custom manufacturer-supplier filter:

-- pivot temp/real table for suppliers - manufacturers
DROP TEMPORARY TABLE IF EXISTS `manufacturers_suppliers`;
CREATE TEMPORARY TABLE `manufacturers_suppliers` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `manufacturer_id` INT(11) UNSIGNED, 
    `supplier_id` INT(11) UNSIGNED,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`manufacturer_id` ASC, `supplier_id` ASC)
);
-- populate pivot table
INSERT INTO `manufacturers_suppliers` (`manufacturer_id`, `supplier_id`)
VALUES 
    -- TechData aren't allowed to sell Microsoft 
    (2, 1), 
    -- hide all Apple products from Westcoast
    (1, 2);

After that, using the content of the container, you only need to create a standard query for your result set.

-- create result
SELECT 
    p.id, p.product,        # show product detail
    s.id, s.supplier,       # show supplier detail
    m.id, m.manufacturer    # show manufacturer detail
FROM
    products p
    INNER JOIN suppliers s ON s.id = p.sup
    INNER JOIN manufacturers m ON m.id = p.man
    LEFT JOIN `manufacturers_suppliers` ms ON 
        ms.manufacturer_id = man.id
        AND ms.supplier_id = sup.id
WHERE ms.id IS NULL;

So that every time you have an update to your filter, you only update records not the actual query script. Cheers

Btw, depending on your requirements, you can either use a `normal` or `temporary` table. — Avidos, Apr 25 '17 at 00:51

Remove results from SELECT by conditions on multiple columns

2 Answers2