背景
作为mySQL n00b，我能想到的匹配三个表的最佳查询是在两个表之间进行比较，输出一个变量，然后使用该变量从交叉引用表中选择我的最终输出。之后，我将运行另一个查询以从第三个表输出。。。
现在我知道有一种方法可以用一句话来选择我需要的所有行，但就我的生活而言，我无法将其拼凑在一起。有人能帮我正确构建我需要的查询吗？
我必须使用来自3个表的信息在php中输出一个结果集，并使用一个单独的结果集作为两个表的id之间的实际链接。非常感谢。
表格
name: table_one
-----------------------------------------------------------
id | user_id | o_id | num | likes | dislikes | .... | ... |
-----------------------------------------------------------
1 | 765 | 1 | 100 |android| cats |
2 | 765 | 2 | 100 | birds | mySQL queries |
3 | 765 | 3 | 100 | php | iPhones |
4 | 765 | 2 | 2 |oranges| bananas |
-----------------------------------------------------------
name: table_two
------------------------------------------------------------|
id |first_name| location | num_times | diploma | why |
------------------------------------------------------------|
1 | ABC | here | 0 | none | because |
2 | BCD | there | 5 | BS | no reason |
3 | Sally | nowhere | 194384 | DR | no reason |
4 | Jack | overthere| 3 | none | failure |
5 | Bob | Mars | 0 | random | in training |
-------------------------------------------------------------
name: table_agency |
---------------------------|
id | name | address |
---------------------------|
1 | A | 123x |
2 | B | 234y |
3 | C | 456z |
----------------------------
name: table_link
-----------------------------
rel_a | rel_b
---------------------------------
1 | 1 |
1 | 4 |
1 | 5 |
2 | 1 |
2 | 4 |
2 | 5 |
3 | 2 |
3 | 3 |
4 | 3 |
---------------------------------
输出/PHP
$results = $class->runQuery($query); //basically a fetchAll
foreach ($results as $result) {
echo id_table_one ($result['id']);
echo $result['name'];
echo $result['num'];
echo $result['likes'];
echo other_rows...basically table_one.*
echo all_first_names&num_times that correspond in the table link;
}
//ACTUAL Printout(echo doesn't have the ,'s):
//here should be the output:
-------------------------------------------------
1 | A | 100 | android | ABC-0/Jack-3/Bob-0|
2 | B | 100 | birds | ABC-0/Jack-3/Bob-0|
3 | C | 100 | php | BCD-5/Sally-194384|
4 | B | 2 | oranges | Sally-194384 |
----------------------------------------------
旁注
1( 将至少有1个交叉引用项目条目与名字相关，最多7个条目(不必在查询中，但仅供参考(
2( table_agency 中只有3个条目
3( 结果集必须通过：user_id=".$variable 进行限定
4( 我最初的查询，或多或少。。。然而，由于我试图提取的额外信息之间没有共同点，我被迫创建一个函数，使平局，。。。。这应该能让我了解我正在努力实现的目标：
$query = "select
a.*,
b.name,
b.id as agency_id
from table_one a, table_agency b
where a.agency_id = b.id
and a.user_id = ".$variable;
现在有了新添加的first_names实际上对应于$result['name']
5( 我可以使用嵌套的foreach(来实际输出。。。first_name的嵌套数组？？？
6( 请对您的查询(回复(进行评论，这样我就可以从您的辛勤工作中学习！我不仅仅是想回答一个问题，还要学习步骤和方法！
7( 提前感谢您的帮助。。。这绝对是一个校长。谢谢
您可以使用此查询获得输出
SELECT
ta.id,
ta.name,
to.num,
to.likes,
GROUP_CONCAT(tt.first_name SEPARATOR '-') AS `names`
FROM table_agency AS ta
LEFT JOIN table_one AS `to` ON to.o_id = ta.id
LEFT JOIN table_link AS tl ON tl.rel_a = to.id
LEFT JOIN (SELECT id , first_name FROM table_two) AS tt ON tt.id = tl.rel_b
WHERE to.user_id = 765
GROUP BY to.id
您可以将php代码中的user_id替换为变量。对于ABC-0/Jack-3/Bob-0，你可以简单地更换这个GROUP_CONCAT(tt.first_name SEPARATOR '/') AS names
SQL Fiddle演示
输出
| ID | NAME | NUM | LIKES | NAMES |
--------------------------------------------
| 1 | A | 100 | android | ABC-Jack-Bob |
| 2 | B | 100 | birds | Bob-ABC-Jack |
| 3 | C | 100 | php | BCD-Sally |
| 2 | B | 2 | oranges | Sally |
编辑：
这是经过编辑的查询。您可以使用MySQL Concat函数
SELECT
ta.id,
ta.name,
to.num,
to.likes,
GROUP_CONCAT(tt.first_name SEPARATOR '/') AS `names`
FROM table_agency AS ta
LEFT JOIN table_one AS `to` ON to.o_id = ta.id
LEFT JOIN table_link AS tl ON tl.rel_a = to.id
LEFT JOIN (
SELECT
id ,
CONCAT(first_name,'-',num_times) as first_name
FROM table_two
) AS tt ON tt.id = tl.rel_b
GROUP BY to.id;
演示
输出
| ID | NAME | NUM | LIKES | NAMES |
--------------------------------------------------
| 1 | A | 100 | android | ABC-0/Jack-3/Bob-0 |
| 2 | B | 100 | birds | ABC-0/Jack-3/Bob-0 |
| 3 | C | 100 | php | BCD-5/Sally-194384 |
| 2 | B | 2 | oranges | Sally-194384 |