Multiple JOINS and duplicates

[eviephillips]

I am having a similar problem. I am using Dreamweaver (MySQL, PHP) and their advanced recordset to select the tables and fields. The recordsets are pulling all the pertinent records. However, when a shirt is in two colors and available in 10 sizes, each color repeats 10 times for each size in the dynamic color drop down list, and the 10 sizes are repeated twice for each color in the size drop down list.

This is from the DETAIL page recordset:[code=sql]
SELECT products.itemID , size.size, color.color, options.option
FROM products
JOIN size
JOIN linksize
JOIN color
JOIN linkcolor
JOIN options
JOIN linkoptions
ON products.itemID =linksize.itemI D AND size.ID=linksiz e.sizeID
AND products.itemID =linkcolor.item ID AND color.ID=linkco lor.colorID AND products.itemID =linkoptions.it emID AND options.ID=link options.optionI D
WHERE products.itemID =colname [/code]I originally linked all the tables with the WHERE command because the styleno was a foreign field in every table. MySQL book told me to do it like this, so I did, but it has the same result.

What can I add to this script to only show the sizes (xsm-6xl) one time instead of multiplying for each color.

I can e-mail you the print screens in a Word doc to help you see what I am talking about. The web page is not published yet so I can't give you the link.
Evie

Please enclose any code within the proper code tags. See the Posting Guidelines on how to do that. - moderator

[chaarmann]

What you want to do is something logically impossible.
First be aware that you need the color repeats for each size, because it can happen that some sizes are not available for some colors. It could happen for example that only red is available for size L, but yellow and red is available for size S. Now we can do 2 solutions depending on following cases

1.) condition: For every color there is exactly the same shirt-size list. That means all possible combinations of colors and sizes are always existing.
Solution: make one query to get all colors, and a second query to get all sizes.
You cannot get all the needed data in one set, that's what I called logically impossible. In your example you get 2 records back for the possible colors. But you need a least 10 records back for all possible sizes. So how do you want to fill out the size-column of the remaining 8 records? Repeat the colors? Fill in NULL? Both solutions are wrong. If you fill in "NULL" then 8 empty lines were showing up in your selection box. And if you program the option box in a way that you avoid these empty values, then you could have easily programmed the option box in a way to avoid double entries.
If you need to return these 2 queries in one result set (which I would not do), you could use construct "select ... UNION select ... " to do it. You would get back 10+2 = 12 rows instead of 10*2=20 rows, which is good for performance. Like this: Let's say the first query returns [a,b,c,...,color], second query returns [a,b,c,...,size]. Then the result records should look like [a,b,c...,is_col or, color_or_size]. Achieve it by joining the queries so that it returns: "[a,b,c,... ,true,color] UNION [a,b,c, ...,false,size]"

2.) condition: for evey color there can be a different shirt-size list.That means not all possible combinations of colors and sizes are existing.
Here you need the "cartesian product", that means you need to return a size list for every color. So you cannot avoid returning 2 *10 records in your example which is a special case then. The normal case would return less records and would be something like: only red is available for size L, but yellow and red is available for size S. Here you would not have 2*2=4 records, but only 2+1=3 records.
In your option-boxes, you cannot avoid programming their behaviour dependent on each other: that means if you select "red", the second option box shows one entry "L" only, but if you select "yellow", the second option box suddenly shows 2 entries: "L" and "S".
You can solve that by creating one option box for each size, and hiding/showing the correct one with javascript. Or better: you only make 2 boxes, but fill the second with data interactivey. That means if you click the first option box, you remove all option items from the second and rebuilt it from the data that you have stored in an array in javascript.

I am having a similar problem. I am using Dreamweaver (MySQL, PHP) and their advanced recordset to select the tables and fields. The recordsets are pulling all the pertinent records. However, when a shirt is in two colors and available in 10 sizes, each color repeats 10 times for each size in the dynamic color drop down list, and the 10 sizes are repeated twice for each color in the size drop down list.

This is from the DETAIL page recordset:[code=sql]
SELECT products.itemID , size.size, color.color, options.option
FROM products
JOIN size
JOIN linksize
JOIN color
JOIN linkcolor
JOIN options
JOIN linkoptions
ON products.itemID =linksize.itemI D AND size.ID=linksiz e.sizeID
AND products.itemID =linkcolor.item ID AND color.ID=linkco lor.colorID AND products.itemID =linkoptions.it emID AND options.ID=link options.optionI D
WHERE products.itemID =colname [/code]I originally linked all the tables with the WHERE command because the styleno was a foreign field in every table. MySQL book told me to do it like this, so I did, but it has the same result.

What can I add to this script to only show the sizes (xsm-6xl) one time instead of multiplying for each color.

I can e-mail you the print screens in a Word doc to help you see what I am talking about. The web page is not published yet so I can't give you the link.
Evie

Please enclose any code within the proper code tags. See the Posting Guidelines on how to do that. - moderator

[chaarmann]

dear Admin,
this entry is double-posted. (see all 2 posts from eviephillips).
And can you please make it its own topic?