distinct data

[bramsey]

I am fairly new to SQL and don't know if I am doing this correctly. My SQL statement is as follows:

sql = "SELECT DISTINCT Products.ProductID, Products.ProductName, Colors.ColorDescription, Sizes.SizeDescription FROM Colors INNER JOIN ((Sizes INNER JOIN (Products INNER JOIN ProductSizes ON Products.ProductID = ProductSizes.ProductID) ON Sizes.SizeID = ProductSizes.SizeID) INNER JOIN ProductColors ON Products.ProductID = ProductColors.ProductID) ON Colors.ColorID = ProductColors.ColorID WHERE Products.ProductID = " & id & " GROUP BY Products.ProductID, Products.ProductName, Colors.ColorDescription, Sizes.SizeDescription;"

It returns:

ProductID ProductName ColorDescription SizeDescription
10 Long Sleeve Shirt Blue SM
10 Long Sleeve Shirt Orange SM
10 Long Sleeve Shirt White SM
10 Long Sleeve Shirt Blue MD
10 Long Sleeve Shirt Orange MD
10 Long Sleeve Shirt White MD
10 Long Sleeve Shirt Blue LG
10 Long Sleeve Shirt Orange LG
10 Long Sleeve Shirt White LG
10 Long Sleeve Shirt Blue XL
10 Long Sleeve Shirt Orange XL
10 Long Sleeve Shirt White XL
10 Long Sleeve Shirt Blue XXL
10 Long Sleeve Shirt Orange XXL
10 Long Sleeve Shirt White XXL

I am using this for a shopping cart application and want to output Long Sleeve Shirt one time to the screen - no problem. I want to have a distinct listing of colors in a pull down box - no problem. I want to have a distinct listing of sizes - problem.

I am using ASP to write the Long Sleeve Shirt out. I then use ASP to loop through the colors. This works because I can order by the color, so I can check to see if the current value of color matches the previous value of color. If it does, skip it. If it doesn't, insert it into the pull down list. However, I can't resort by size. Because of this my loop will not work because the current size will never equal the previous size so every size will be inserted into the pull down multiple times.

Is there a way to get only unique values for each column? In other words can I retrieve Long Sleve Shirt (one time), blue (one time), orange (one time), white (one time), SM (one time), MD (one time), LG (one time), XL (one time), and XXL (one time). If there is a way to do that, everything would work perfectly.

Thank you for any help or direction you can provide.

 

[rac2]

Maybe do three queries, one to get product name and possibly other descriptive information about the product; a second query to get colors available for that product; and a third to get get the sizes available. Use the recordsets generated by the second and third queries to build the drop-down boxes.

 

[bramsey]

Thanks for the response. I was thinking about doing that. However, won't that use alot of the database's resources? Will that affect site performance?

 

[justsql]

I all depends on your app server connection pooling capability. Sometimes joins takes more resource because it has to traverse through three tables to come back with the result set but in the case of three queries..the only drawback is it has to connect to get the results...

 

[bramsey]

Thanks ror the replies. Based on what I have heard, I am going to make the page using several queries. I appreciate the feedback.