Adjacent table with two keys

Код ОС и КодПродавца 1 12 1 11 2 12 2 11

Table Seller

 КодПродавца Название 11 Сток 12 Стол

How to write the correct request to display the OS code , Name Received (deduce from the table Seller.name , where Adjacent.Code of Seller = Seller.Code of Seller , and in the title, all the values, where the OS Code is repeated)?

The result should be like this.

 КодОС НазваниеПолученное 1 Сток,Стол 2 Сток,Стол
  • What exactly you can not? - orkaan

4 answers 4

Create a function that, according to the OS Code, passes through the union of the tables and returns a list of names separated by a comma. Then make a query returning the DISTINCT from the OS codes and the associated names glued to it.

In general, I have a feeling that this data is optimally processed on the client.

Generally, pasting names separated by commas is an aggregate function by semantics.

     SELECT table1.field, table1.field2, table2.field3 FROM table1 LEFT JOIN table2 ON table1.field_key = table2.field_key;

    In your case:

     SELECT `Смежная`.`Код ОС`, `Продавец`.`Название` as `Название полученное` FROM `Смежная` LEFT JOIN `Продавец` ON `Смежная`.`КодПродавца` = `Продавец`.`КодПродавца`;

    Instead

     ON `Смежная`.`КодПродавца` = `Продавец`.`КодПродавца`

    in your case, you can use

     using(`КодПродавца`)

      In MySQL, I would have made GROUP_CONCAT , I don’t know if you have any analogue:

       SELECT `Смежная`.`Код ОС`, GROUP_CONCAT(`Продавец`.`Название`) as `Название полученное` FROM `Смежная` JOIN `Продавец` ON `Смежная`.`КодПродавца` = `Продавец`.`КодПродавца` GROUP BY `Смежная`.`Код ОС`;

      Otherwise, only on the client to glue. LEFT JOIN needed only if you also need to display those OS for which there are no sellers at all.

         SELECT distinct p1.[Код ОС], ( SELECT [Название] + ',' FROM [Продавец] p2 inner join [Смежная] p3 on p2.[КодПродавца] = p3.[КодПродавца] WHERE p1.[Код ОС] = p3.[Код ОС] ORDER BY [Название] FOR XML PATH('') ) AS [НазваниеПолученное] FROM [Смежная] p1

        The code works since MS SQL 2005, if older versions are needed, see here: http://www.sql.ru/faq/faq_topic.aspx?fid=130 If you know English, you can see a large review here: http: // www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/