需要一个mysql查询推荐

Question

Assuming this is possible, I'm looking for a method(MYSQL query) to join tables where none of the values returned are duplicated. I would like to join three tables (A, B, C). The common column among the three tables is the user_id. The user_id will be unique in Table A - users will only have one row/record in Table A. However, the user_id will not be unique in Tables B & C - users may have several records/rows in both tables B & C. I've tried INNER, LEFT, and RIGHT JOINS, as well as UNION However, it duplicates the results from Table A to match with the results from Table B & C.

Row 1:    Value1(from A)        Value1(from B)        Value1(from C)
Row 2:    Value1(from A)        Value2(from B)        Value2(from C)  
Row 3:    Value1(from A)        Value3(from B)        Value3(from C)

I want to avoid this. Instead I would prefer:

Row 1:    Value1(from A)        Value1(from B)        Value1(from C)
                                Value2(from B)        Value2(from C)  
                                Value3(from B)        Value3(from C)
Row 2:    Value2(from A)        Value1(from B)        Value1(from C)
                                Value2(from B)        Value2(from C)  
                                Value3(from B)        Value3(from C)

Thanks in advance for any help.

Answer 1

If your main interest is in having each row return all of the other values in B and C, and you aren't fussed about the number of columns, then the following might be useful:

SELECT A.F1, GROUP_CONCAT(B.F1) AS all_b_F1, GROUP_CONCAT(C.F1) AS all_c_F1 FROM A INNER JOIN B USING (user_id) INNER JOIN C USING (user_id) ORDER BY A.F1;

Note that I've made up field names since you fidn't specify them. You can add additional GROUP_CONCAT columns for other fields in B and C or alternatively CONCAT or CONCAT_WS them together in place of B.F1 in current query.

Depending on what you want to do with the results this query might be good enough. For example, you could explode all_b_F1 in PHP to get the indiividual values, or export the query results from MySQL to a csv using FIELDS ENCLOSED BY '' so that each value in B and C gets their own cell when the csv is imported into Excel.