php - 使用另一个交叉引用组合三个表输出

Question

背景

作为 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( 实际输出 ...nested array for first_name 的最终结果集???

6) 请对查询(回复)进行评论，以便我可以从您的辛勤工作中学习!我不仅要回答问题，还要学习步骤和方法!

7) 预先感谢您的所有帮助...这绝对是一个令人头疼的问题。谢谢!!!

Answer 1

你可以得到这个查询的输出

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 Demo

输出

| 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; 

Demo

输出

| 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 |