CODEWITHSUNDEEP
mysql
Thanh Phong
Thanh Phong

Reputation: 33

MySQL query get product have multiple color and order by number of color product have

I have one table product(id, name) have n-n relationship with table color(id, name), and 1 table product_color(product_id, color_id). How can i query all product can have multiple color (example i want query product can have color red, green, blue) and order them by number of color coincident( product have 3 color red, green, blue will be first, then product with 2 color and 1 color last)

Upvotes: 0

Views: 696

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

you could use a select form the joined table and count the number of distinct color

  select prod_name, count(distinct color_name)
  from ( 

        select 
              a.id  prod_id
            , a.name prod_name
            , b.id color_id
            , b.name color_name
        from product a
        inner join product_color c on a.id  = c.product_id
        inner join color b on b.id = c.color_id
        where b.name in ('red', 'green', 'blue')

  ) t 
  group by prod_name
  order by count(distinct color_name) desc

Upvotes: 1

Related Questions