Have two different form only one field, two tables have the same foreign keys, associated with a table id, b table id
Table 1: id, name, a_id, b_id
Table (2) : id, value, a_id, b_id

Now I want to get a result: according to the a_id, b_id grouping, table lookup table (1) the name field and (2) a collection of the value field, with GROUP_CONCAT splicing
Could you tell me how to use writing to get correct results
Table (table 2) was originally a piece of table, in order to put the name and the value field apart, so split into two tables

CodePudding user response:

Join a good ah,

CodePudding user response:

If just to break the value and the name, why two foreign key id
The original table A: id,
Table 1: id, name, a_id
Table (2) : id, value, a_id
It doesn't work?

CodePudding user response:

Table 1 and table 2 are a_id, b_id to distinguish,
Open before the SQL is probably
SELECT
GROUP_CONCAT (t1) name),
GROUP_CONCAT (t1) value)
The FROM
T1
INNER JOIN a ON Anderson, d=t1. Id
INNER JOIN b ON b.i d=t1. Id

Now apart, the relationship between the two tables is that they can to a_id, b_id to determine the relationship between

CodePudding user response:

The SQL is the desired effect, statistics (1) all the value of the name field, then stitching together, statistics (2) the value field, stitching together, use a_id, b_id group, is to make the table (table 2) a_id, reach a polymerization b_id the effect of the primary key

CodePudding user response:

Send to the sample data and the desired results,

CodePudding user response:

Whether that is

SELECT
Group_concat (t_all. ` name `, ', '),
Group_concat (t_all. ` value `, ', ')
The FROM
(
SELECT
TV. ` value `,
Tn. ` name `,
Tn a_id,
Tn. B_id
The FROM
Test_n tn
LEFT the JOIN test_v TV ON tn. A_id=TV. A_id
AND tn. B_id=TV. B_id
) t_all
GROUP BY
T_all. A_id

CodePudding user response:

refer to 6th floor human response: it's light brown.

is the

SELECT
Group_concat (t_all. ` name `, ', '),
Group_concat (t_all. ` value `, ', ')
The FROM
(
SELECT
TV. ` value `,
Tn. ` name `,
Tn a_id,
Tn. B_id
The FROM
Test_n tn
LEFT the JOIN test_v TV ON tn. A_id=TV. A_id
AND tn. B_id=TV. B_id
) t_all
GROUP BY
T_all. A_id

may duplicate, they only want to be with DISTINCT to heavy