Hello!
I have three tables!

news

id|title|text|test|qwer|trrr 1|test name|description|wer234|qqq 2|test nam2|description|wer234|qqq 3|test na3|description|wer234|qqq 4|test n4|description|wer234|qqq 5|test 5|description|wer234|qqq

type

 id|type 1|type1 2|type2 3|type3 4|type4 5|type5

newsandtyp

  news_id|type_id 1|2 1|5 1|1 2|3 2|4 5|5 5|2 5|3

The newsandtype table is a many-to-many relationship, the news and type relationship is so, how can I correctly select records from the news table, given the type table? For example, I want to display:

 1 test name description type2,type5,type1 wer234 2 test nam2 description type3,type4 wer234 5 test 5 description type5,type2,type3 wer234

display data from the news table + data from the type table only if there are links in the newsandtype table

  • only if there are connections - INNER JOIN - Akina

1 answer 1

You will need to build a query using the JOIN, the query schema is shown below in Figure 1:

Fig.1 intersection operation

To do this, we perform the intersection operation INNER JOIN. The output of entries from the news table will depend on the newsrandtype table, and the newsrandtype table itself will overlap with the type table. The green area in the diagram denotes the operation INNER JOIN.

Here such a request will display all the data, but here news can be repeated depending on the number of types, but this can be processed on the server side.

 SELECT `news`.*, `type`.* FROM `news` JOIN `newsandtype` ON `newsandtype`.news_id = `news`.id JOIN `type` ON `type`.id = `newsandtype`.type_id;

In another case, you can make a grouping and output types, separated by commas, in the types field:

 SELECT `news`.*, GROUP_CONCAT(`type`.`type`) types FROM `news` JOIN `newsandtype` ON `newsandtype`.news_id = `news`.id JOIN `type` ON `type`.id = `newsandtype`.type_id GROUP BY `news`.id;
  • thanks) helped - John Freeman