General SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesGeneral SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old January 23rd, 2013, 01:16 AM
pascalVG pascalVG is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 1 pascalVG User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 38 m 39 sec
Reputation Power: 0
SQL that returns 'relative complement' of two subsets

Hi all,

(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:
id_product, id_category:
prod1, cat1
prod1, cat2
prod1, cat3
prod2, cat2
prod2, cat3
prod3, cat3

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)

Thanks!

Pascal

Last edited by pascalVG : January 25th, 2013 at 12:48 AM. Reason: Messed up question. Try again...

Reply With Quote
  #2  
Old January 26th, 2013, 05:49 PM
bitsmed bitsmed is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2012
Posts: 11 bitsmed User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 59 m 53 sec
Reputation Power: 0
Maybe this will work for you:
Code:
select id_product
  from link_table
 group by id_product
 having sum(case when id_category='cat3' then 1 else 0 end)>0
    and sum(case when id_category in ('cat1','cat2') then 1 else 0 end)=0
The above sql has not been tested, as I currently only have access to notepad

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesGeneral SQL Development > 'overlapping' product rows


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap