Rohdekill Posted August 9, 2009 Share Posted August 9, 2009 I have the following tables: [PRODUCTS] PROD_ID TITLE [colorS] ID COLOR [PRODtomanyCOLORS] PROD_ID ID I would like to query in such a way where if a user wants "blue" that the Products display in such a way that items ONLY marked blue show first, then products that have blue and additional colors follow. Is there a single query that can do this? Link to comment Share on other sites More sharing options...
0 Ankur A. Patel Posted August 9, 2009 Share Posted August 9, 2009 SELECT a,b,c CASE COLOR BLUE THEN 0 ELSE 1 As Ordering END CASE FROM (WHATEVER TABLES) ORDER BY Ordering This is not correct syntax, but it gives you the general idea. Link to comment Share on other sites More sharing options...
0 Rohdekill Posted August 9, 2009 Author Share Posted August 9, 2009 Ok, I get the idea. I completely forgot about CASE. Thanks! Link to comment Share on other sites More sharing options...
0 atrotter01 Posted August 9, 2009 Share Posted August 9, 2009 I think this will work for MySQL: select prod.TITLE, count(*) as count from PRODUCTS as prod join PRODtomanyCOLORS prod_colors on prod_colors.PROD_ID=prod.PROD_ID join colors on colors.id=prod_colors.id where exists(select * from PRODtomanyCOLORS join COLORS on COLORS.id=PRODtomanyCOLORS.id where PRODtomanyCOLORS.PROD_ID=prod.id and COLORS.color='Blue' ) group by prod.PROD_ID order by count ASC; The where exists will filter out any products that do not have blue as a color, then the group by and count will give you a count of how many colors that product has. Thus we know that it has blue as a possible color, and if we order by count asc, we will get products which only have blue first (count == 1). Link to comment Share on other sites More sharing options...
Question
Rohdekill
I have the following tables:
[PRODUCTS]
PROD_ID
TITLE
[colorS]
ID
COLOR
[PRODtomanyCOLORS]
PROD_ID
ID
I would like to query in such a way where if a user wants "blue" that the Products display in such a way that items ONLY marked blue show first, then products that have blue and additional colors follow.
Is there a single query that can do this?
Link to comment
Share on other sites
3 answers to this question
Recommended Posts