Funky many-to-many select

[philwilks]

I have three tables:

• products (prodId, prodName, description, price)
• classifications (classId, className)
• junctions (prodId, classId)

The products table stores the details of various t-shirts.

The classifications table stores all of the differant attributes that a t-shirt might have. For example: Small, Medium, Large, Black, White, Blue, Pink, V-neck, Round neck, Short sleeved, Long sleeved etc.

The junctions table sets up a many-to-many relationship between products and classifications. The idea is that any given t-shirt in the products table could have any number of the attributes in the classifications table. For example, one t-shirt could be available in two different sizes and three different colors.

I want to set up a system so that the user can choose a range of classifications, and then filter the products table accordingly to show which t-shirts match their criteria. A typical requirement might be:

Large and (White or Pink) and V-neck

Any suggestions? I have everything else set up fine, I'm just struggling with formulating an SQL SELECT statement to do the query.

[Tangerine_Dream]

Hi, maybe this will help:

SELECT p.prodName, p.description, p.price FROM products p
  INNER JOIN junctions j ON p.prodId = j.prodId
  INNER JOIN classifications c ON j.classId = c.classId 
  WHERE c.className = 'Large' AND c.className = 'V-neck' AND ( c.className = 'White' OR c.className = 'Pink'  );

[r937]

Originally Posted by Tangerine_Dream

  WHERE c.className = 'Large' AND c.className = 'V-neck'

that's never going to return anything!!!

[r937]

this should work for the given example (since presumably an item cannot be both white and pink) --

select p.prodName
     , p.description
     , p.price 
  from products p
inner 
  join junctions j 
    on p.prodId = j.prodId
inner 
  join classifications c 
    on j.classId = c.classId 
group
    by p.prodName
     , p.description
     , p.price 
having sum(case when c.className 
             in ('Large','V-neck','White','Pink')
                then 1 else 0 end)
       = 3

however, you might want to think about how to generalize this a bit more for an arbitrary set of AND/OR search conditions

[philwilks]

Thank you both for your advise!

Tangerine Dream: Rudy is right - your query will never return anything. This is exactly what I tried to begin with, before I posted on this forum.

Rudy: I think you have grasped my problem - thank you very much for your example code! Unfortunately, I cannot assume that an item will not be both white and pink. For example, a "Bart Simpson" t-shirt might be available in Child, Small and Medium sizes, and in Blue, White or Pink.

Any ideas?!?

PS: I should have explained that I will be filtering the database using classId, not className. I only used the name in my question to make my problem easier to understand. Anyway, that's not a big deal.

[r937]

okay, then

having sum(case when c.className 
             in ('Large','V-neck')
                then 1 else 0 end)
       = 2          
   and sum(case when c.className 
             in ('White','Pink') 
                then 1 else 0 end)
       > 0