0

I am doing a mysql query using 2 tables and I am trying to only get a maximum of 3 rows per group.

The example is if table 1 is Departments and table 2 is Categories. Lets say a Department is Clothing and another Department is Jewellery. If the Department named Clothing has 5 Categories (Shoes, jeans, underware, socks, sweatshirts) and the Department Jewellery has 4 Categories (rings, necklaces, bracelets, earrings) how do I return only a maximum of 3 Categories for each Department.

I want the output to return

Department   Category
______________________________________
Clothing     Shoes
Clothing     Jeans
Clothing     Underware
Jewellery      Rings
Jewellery      Necklaces
Jewellery      Bracelets

I only want to return a maximum of 3 Categories for each Department.

This is an example of what I am trying to do but there are many more departments and categories in the database. I will do an ORDER BY Category and Department. This query is to display a short piece of info to the user so later I will have to access all the Departments and Categories. I can already do that with mysql but I cant figure out how to just get 3 Categories MAX only

Miff
  • 15
  • 7

1 Answers1

0

One possibility is to use simple SELECT statements with a LIMIT and use UNION to join the data together. For example:

Departments Table:

+----+--------------+
| id | name         |
+----+--------------+
|  1 | jewelry      |
|  2 | clothing     |
+----+--------------+

MySQL:

(SELECT * from categories WHERE department_id=1 LIMIT 3)
UNION
(SELECT * from categories WHERE department_id=2 LIMIT 3)

However, if you have a lot of different departments, you will want a more robust approach. In which case this answer will help you: https://stackoverflow.com/a/10516530/11182541

Eden Dowling-Mitchell
  • 271
  • 1
  • 5
  • Note that LIMIT without ORDER BY is a fairly meaningless concept – Strawberry Feb 27 '20 at 06:01
  • Departments and Categories are in different tables and there are many more Departments and Categories in the database – Miff Feb 27 '20 at 08:19
  • @Miff yep that's why I said "if you have a lot of different departments, you will want a more robust approach" and then linked a relevant answer for that scenario :) – Eden Dowling-Mitchell Feb 27 '20 at 10:29