• 0

[SQL] Advanced Query


Question

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

  • 0

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

  • 0

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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.