0

Here is the DB Schema I am using (Many to Many relationship between clients and products via junction table client_product):

TABLE client
- clientid
- clientname

TABLE product
- productid
- productname
- category

TABLE client_product
- clientid*
- productid*

Category is an element of a fixed set (for this example, the set is {Food, Beverage, Books}).

I'm trying to find the most efficient way to get, for each client, his id and name, as well as (and this is the tricky part) the list of all the products he bought, sorted by category (to be displayed on a webpage via PHP, but I don't know if this is relevant). Here is a simple example of what I'd like to display:

ClientID-----Client Name-----Food------------Beverages-------------Books
-----1--------------John----------Crisps-----------Coke; Fanta----------MySQL for dummies
-----2--------------Alex-----------Pizza------------__________----------Deadpool

Now that I think about it, I'm wondering if it'd be easier to create a new table to store the categories, and add a foreign key constraint on products.category

I tried several queries but couldn't find a satisfying solution.

Ross
  • 1,313
  • 4
  • 16
  • 24
Manu1583
  • 1
  • 3
  • *"Here is a simple example of what I'd like to display:"* Consiver displaying issues to be handled in PHP.. Otherwise check MySQL's [GROUP_CONCAT()](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat) which can generate delimited strings like that – Raymond Nijland Mar 28 '19 at 14:15
  • 1
    Inner joins to start with? – nice_dev Mar 28 '19 at 14:16
  • OK, let me be q little more specific, my apologies. Handling will be handled by PHP, I'm only trying to get these columns as a result of a query, therefore avoiding executing several queries per client. Also, the category set contains approximately 40 elements and I'd rather avoid hard coding each category column of the output. – Manu1583 Mar 28 '19 at 14:17
  • 1
    it looks like [pivotting](https://stackoverflow.com/questions/7674786/mysql-pivot-table) is also required here because `Food------------Beverages-------------Books` seams to be `categories` from the `product.category` column .. See [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) for providing example data,, – Raymond Nijland Mar 28 '19 at 14:19

0 Answers0