January 23rd, 2013, 01:16 AM
Join Date: Jan 2013
Time spent in forums: 38 m 39 sec
Reputation Power: 0
SQL that returns 'relative complement' of two subsets
(updated question. Some nonsensical errors in old one)
Have the following question.
I have a link table with id_product and id_category.
table contents maybe someting like this:
assume that cat1 and cat2 are "Main categories" and cat3 is a sub category of cat1 (or cat2 for that matter)
What I need is all products that DON'T have any main categories bound to it, (i.e. in above example I need only prod3 as a result as prod1 is in cat1/cat2 and prod2 is in cat2).
I had something in mind like:
Set1: All items that are in a main category
select * from link_pr_cat where _id_category IN (cat1, cat2)
Set2: All rows that are not in any main category
select * from link_pr_cat where _id_category NOT IN (cat1, cat2)
What I need is all products in set2, that have an id_product not found anywhere in set1. (is this 'relative complement' of set2??) I thinks something like:
Select set2.* from set1, set2 where NOT EXISTS (set2.id_product = set1.id_product)
or so. (I hope)
Can anyone create a SINGLE SQL (MySQL) query that should do something like this and/or gives me the correct result? I tried and tried but stumble...
(Background: I want to find all products that I put in subcategories, but where I forgot to put it in the main category as well)
Last edited by pascalVG : January 25th, 2013 at 12:48 AM.
Reason: Messed up question. Try again...