Funky many-to-many select statement

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.
philwilksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EMCITCommented:
Generally speaking your "Classifications" should be further broken down. There looks to be sizes, styles and colours within the className field. Since each size can be in any colour or any style I would create tables for Size, Style, Colour as well as any additional differences. Fields in your products table might then include - size, style, colour, etc. which allows very comprehensive filtering. With this structure you could have a "Product Look-Up" form with combo boxes for "Size", "Colour" and "Style". After setting these criteria you can query the Products table for those items matching the criteria.
philwilksAuthor Commented:
I agree that that would be the most "standard" approach, and would make the filtering very simple. However, the difference with my database is that each t-shirt is available in several sizes and several colors.

For example, a "Bart Simpson" t-shirt might be available in Child, Small or Medium sizes, and in Blue or White. I can display the options available for a given t-shirt by doing SELECT classId FROM classifications WHERE prodId = 123. What I need to do is essentially a reverse of this - given a list of classifications, show which t-shirts are available.
EMCITCommented:
Wel you can create a query and pull the products and classifications tables into it. With no joins between the tables put the productID and classID and className into the query fields. When run this should generate a set of all possible permutations.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

philwilksAuthor Commented:
As an example, would you be able to post an SQL statement that would do this for the following requirements:

(Small or Medium) and (White or Pink) and Vneck

Thanks.

EMCITCommented:
SELECT Colour.Colour, Size.Size, Style.Style
FROM Colour, [Size], Style;

Will yield:

Colour      Size      Style
White      Small      V-Neck
Pink      Small      V-Neck
White      Medium      V-Neck
Pink      Medium      V-Neck

This is based on having tables for Colour, Size and Style, though. I don't fully understand how the "Description" field in your products table might read.
EMCITCommented:
The same query would yield 200 results if you had 10 colours, 4 sizes and 5 styles to choose from.
philwilksAuthor Commented:
An example entry in the products table might read:

prodId: 123
prodName: Bart Simpson
description: Bart saying "Don't have a Cow Man"
price: 12.99

Perhaps it would have been better for me to call the products table "designs".
jifang74Commented:
This is not possible for the current database setup.  You'll not be able to tell how many white t-shirts are Vneck and size small.

you'll have to change the Junction table to
junctions (prodId, color, size, neck, count)
where color, size, and neck are all foreign key to classifications.classId

Now you can do the query.

JF
philwilksAuthor Commented:
EMCIT, I think you are missunderstanding my question.

First, the colors, sizes etc are not mutually exclusive: one product may be available in several sizes, whilst still only having one entry in the products table.

Second, I need to return a list of products, not classifications. The products that are returned will be filtered by the classifications given by the user.
philwilksAuthor Commented:
jifang74, I can't really do that since I need to be able to add new classifications without altering the database schema. For example, I might want to add a "sex" classification (male or female) in the future, and I need the database to be flexible enough that the structre can remain unaffected.

I can't believe that this is impossible using my proposed structure. If I can go through the data "manually" and deduce what the result of the query should be, there MUST be a way of automating it with SQL!!

jifang74Commented:
In your Junctions table, it only tells you the types of colors, sizes, and Vnecks the product has.  There is no relationship between those classifications.  You'll not be able to tell me how many count of a product with size:small, color: blue, and Vnecks.  You need a relationship table that makes that correlation.
jifang74Commented:
philwilks,

that is a tough one.  The only thing that I can think of is create extra fields in the Junctions table:

Junctions(prodid, color, size, neck, count, classID1, classID1,...)

good luck!
JF
EMCITCommented:
I don't understand how you can return a list of products based on classifications given by the user. There are no classifications associated with any of your products.
EMCITCommented:
Try this:

SELECT classifications.classID, classifications.className, products.productID, products.prodName
FROM classifications, products;
Vadim RappCommented:
Let's say, the user has selected several Classifications, which results in creating a temporary table Selections (classid)

Then, your sql statement is:

select distinct productname,description,price
from products
inner join junctions on products.prodid = junctions.prodid
inner join classifications on junctions.classid=classifications.classid
inner join selections on selections.classid = classifications.classid

This represents the case when user's selections are "or": large or v-neck or pink ; each selection = one row in selections table.

Each "and" will add another Selections table, joined to other tables the same way:

select distinct productname,description,price
from products
inner join junctions on products.prodid = junctions.prodid
inner join classifications on junctions.classid=classifications.classid
inner join selections1 on selections1.classid = classifications.classid
inner join selections2 on selections2.classid = classifications.classid

i.e. when the user specifies the slections, you have to create as many Selections tables as there are "and"'s in the criteria , plus 1, and get Classifications that join to all those tables.




philwilksAuthor Commented:
Vadimrapp1, you have grasped my problem perfectly! Thank you very much for your example code.

Before I award the points, how would you suggest that I create the selections tables on the fly? Would I have to execute several SQL statments? If it helps, I am using MySQL 4.1.

Thanks again.
Vadim RappCommented:
I don't see any other way but to parse the user input; but I would think you already parse it iin order to build the request itself, so creating these tables whenever you see another predicate of "and" shouldn't be too hard:

NextSelection = NextSelection + 1
executesql "create table selection" & NextSelection  & " (classid int)"
executesql "insert into selection" & NextSelection  & " values (" & user-selected-class-id & ")"

If the predicate is not one value but "(value1 or value2)", then there would be two INSERT's.

The above is a "draft" giving the idea, not a real statement. I'm not familiar with mysql. In sql server, the above might be one statement (a batch).
Vadim RappCommented:
..on 2nd thought, you can have only one table selection (ckassid, predicate), wehre predicate column woul enumerate the "and" conditions:

large and (white or pink) ->

large 1
white 2
pink 2

->

select distinct productname,description,price
from products
inner join junctions on products.prodid = junctions.prodid
inner join classifications on junctions.classid=classifications.classid
inner join selections selections1  on selections1.classid = classifications.classid and selections1.predicate=1
inner join selections selections2 on selections2.classid = classifications.classid and selections2.predicate=2


I did not test this, but it looks like it might work.




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.