0

I have this structure of tables: menu

id | name
1  | Electronics
2  | Cars
3  | Bikes
4  | Music

products

id | brand_name | product_name | price | description | ...
1  | Lenovo     | Notebook ABC | 800   | ...         | ...
2  | Apple      | iPad Mini    | 400   | ...         | ...
3  | Bonton     | Taylor Swift | 15    | ...         | ...
4  | BMW        | BMW 3 2010   | 30000 | ...         | ...

categories

id_product | id_menu
1          | 1
1          | 7
1          | 9
4          | 2
3          | 10

And I am trying to print out for every brand all its categories and from each category to print out 10 products order by the highest price, so the output would look like this:

Apple - Electronics - iPad Mini - 400
Apple - Electronics - iPad 2 - 300
Apple - Electronics - iPad - 180
Bonton - Music - Taylor Swift - 15
Bonton - Music - U2 - 13
...

The lack of the table with all brands makes my life tougher, so I'll probably add it to the scheme. But could I ask you guys for helping me with a query, that would fetch the information as I outlined?

Thank you very much, it looks too much for me right now.

EDIT:

As I use PHP, I think that it can be also in 2 queries (I am unable to do it with one query, but struggling also with 2 MySQL queries in PHP)

user2932090
  • 331
  • 2
  • 7
  • 16

1 Answers1

0

Try

set @num := 0, @brand := '';

SELECT p.brand, m.name,p.name, p.price, @num := if(@brand = p.brand, @num + 1, 1) AS row_number, @brand := p.brand AS dummy FROM category c INNER JOIN products p ON p.id = c.id_product INNER JOIN menu m ON m.id = c.id_menu GROUP BY p.brand, p.price, p.name HAVING row_number < 10 ORDER BY p.price DESC;

Not sure it this do exactly what you are expecting. Hope it helps.

ppolak
  • 641
  • 5
  • 8