There are 3 tables:

1 клиент 2 продукт 3 заказ

Customer

 id name

Product

 id productname

Order

 id clientId prdouctId

Let the contents of the client table

 1 Валера 2 Маша 3 Валера

Product

 1 Хлеб 2 Соль

Order

 1 1 1 (1 Валера Хлеб) 2 1 2 (2 Валера Соль) 3 2 2 (2 Маша Соль) 4 3 1 (2 Валера Соль)

How can I display all products from clientId = 1? For example, to be | Valera | Bread | Salt?

  • To one request in one line lists the goods (for example, separated by commas)? Or in different lines (records), and will you list them programmatically? - Ella Svetlaya am
  • And by the way, do you have MySQL or MS SQL? - Ella Svetlaya

3 answers 3

More correctly , from the point of view of the principle of separation of logic and representation, it will be easy to make a sample:

 SELECT `Клиент`.`name`, `Продукт`.`productname` FROM `Заказ` LEFT JOIN `Клиент` ON `Заказ`.`clientId` = `Клиент`.`id` LEFT JOIN `Продукт` ON `Заказ`.`prdouctId` = `Продукт`.`id` WHERE `Заказ`.`clientId` = 1

So you get all the lines with user products. Then you can present them in the form in which you want and it is easy to change this view without changing the request itself.

If you want to get all the data in one line, use the answer @cheops.

    You can do the following:

     SELECT c.name AS client, GROUP_CONCAT(p.productname) AS products FROM clients AS c LEFT JOIN clients_products AS cp ON c.id = cp.clientId LEFT JOIN products AS p ON p.id = cp.prdouctId WHERE cp.clientId = 1 GROUP BY c.id 
       SELECT c.Name, p.productname FROM Заказ AS z INNER JOIN Клиент AS c ON z.clientId = c.id INNER JOIN Продукт AS p ON z.productId = p.id WHERE z.clientId = 1;

      It will look like this:

       |Валера|Хлеб| |Валера|Соль|