php - 表的左连接获得如此多的记录。 php pdo mysql

Question

"SELECT *,t1.pin AS table1.1pin"
        . ",t3.pin AS table2.1pin"
        . ",t6.pin AS table3.1pin"
        . ",t9.pin AS table4.1pin"
        . ",t2.tin AS table1.1tin"
        . ",t2.first_name AS table1.1firstname"
        . ",t2.last_name AS table1.1lastname"
        . ",t2.middle_name AS table1.1middlename"
        . ",t2.suffix AS table1.1suffix"
        . ",t5.tin AS table2.1tin"
        . ",t5.first_name AS table2.1firstname"
        . ",t5.last_name AS table2.1lastname"
        . ",t5.middle_name AS table2.1middlename"
        . ",t5.suffix AS table2.1suffix"
        . ",t8.tin AS table3.1tin"
        . ",t8.first_name AS table3.1firstname"
        . ",t8.last_name AS table3.1lastname"
        . ",t8.middle_name AS table3.1middlename"
        . ",t8.suffix AS table3.1suffix"
        . ",t10.tin AS table4.1tin"
        . ",t10.first_name AS table4.1firstname"
        . ",t10.last_name AS table4.1lastname"
        . ",t10.middle_name AS table4.1middlename"
        . ",t10.suffix AS table4.1suffix"
        . ",t1.effectivity_qtr AS table1qtr"
        . ",t1.effectivity_year AS table1year"
        . ",t4.effectivity_qtr AS table2qtr"
        . ",t4.effectivity_year AS table2year"
        . ",t7.effectivity_qtr AS table3qtr"
        . ",t7.effectivity_year AS table3year"
        . ",t9.effectivity_qtr AS table4qtr"
        . ",t9.effectivity_year AS table4year"
        . " FROM "
        . "table1 AS t1 "
        . "LEFT JOIN table1.1 AS t2 ON t1.pin = t2.pin AND t1.status = t2.status "
        . "LEFT JOIN table2 AS t3 ON t1.pin= t3.table2_pin AND t1.status = t3.status "
        . "LEFT JOIN table2.1 AS t4 ON t3.pin = t4.pin AND t3.status = t4.status "
        . "LEFT JOIN table2.2 AS t5 ON t3.pin = t5.pin AND t3.status = t5.status "
        . "LEFT JOIN table3 AS t6 ON t1.pin = t6.table3_pin AND t1.status = t6.status "
        . "LEFT JOIN table3.1 AS t7 ON t6.pin = t7.pin AND t6.status = t7.status "
        . "LEFT JOIN table3.2 AS t8 ON t6.pin = t8.pin AND t6.status = t8.status "
        . "LEFT JOIN table4 AS t9 ON t1.pin = t9.pin AND t1.status = t9.status "
        . "LEFT JOIN table4.1 AS t10 ON t1.pin = t10.pin AND t1.status = t10.status "
        . "WHERE "
        . "t1.pin LIKE '%$pin%' AND t1.status = 'Active' ";

我有 10 个表的左联接，其中 4 个表各有 3 行，其余表只有一行。我想获取 10 个表中的所有行，所以我使用了 LEFT JOIN 以防其中一个表没有记录，因此会返回一些内容。目前我的表由 1 行组成，除了 4 个表，每个表有 3 行，所以我希望总共有 12 行。但是，当我回显 rowCount() 时，我得到了 81 行，它的更像是 3 X 3 X 3 X 3。

1. 为什么会这样。
2. 我如何按顺序进行正确的选择查询或获得 12 个所需的行。

任何建议表示赞赏。

仅供引用

也尝试过联合，但我认为在我的情况下这是不可能的，我只需要记录它们是否存在，因为我知道如果有空表，联合将添加空值。

附加信息

1. t1.pin 作为 pin 存在于 t1.1 中
2. t1.pin 作为 table2_pin 存在于 t2 中(t2 具有列 pin，然后存在于 t2.1 中)
3. t2.pin 作为 pin 存在于 t2.1 中
4. t2.pin 作为 pin 存在于 t2.2 中
5. t1.pin 存在于 t3 中作为 table3_pin(t3 具有列引脚，然后存在于 t3.1 中)
6. t3.pin 作为 pin 存在于 t3.1 中
7. t3.pin 作为 pin 存在于 t3.2 中
8. t1.pin 作为 pin 存在于 t4 中
9. t1.pin 作为 pin 存在于 t4.1 中

DEMO HERE

UPDATED DEMO HERE

更新我想要实现的是从表中获取满足我放入 like 1 的示例中的 where 子句的所有数据。简而言之，我想获取表中的所有数据。

pin                          effectivity_qtr    effectivity_year    status      tin             last_name   first_name  middle_name suffix      
015-08-0011-000-01           1st                2013                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01           1st                2014                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01           1st                2015                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01-1001      1st                2013                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01-1001      1st                2014                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01-1001      1st                2015                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01-2001      1st                2013                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01-2001      1st                2014                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01-2001      1st                2015                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01           1st                2013                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01           1st                2014                Active      342-432-423-000 Smith       John        James       Jr  
015-08-0011-000-01           1st                2015                Active      342-432-423-000 Smith       John        James       Jr  

更新的预期输出

sysid   pin effectivity_qtr effectivity_year    status  sys_id  tin pin last_name   first_name  middle_name suffix  status  sys_id  pin table2_pin  status  sysid   pin effectivity_qtr effectivity_year    status  sys_id  tin pin last_name   first_name  middle_name suffix  status  sys_id  pin table3_pin  status  sys_id  pin status  effectivity_qtr effectivity_year    sys_id  tin pin last_name   first_name  middle_name suffix  status  sys_id  pin status  effectivity_qtr effectivity_year    sys_id  tin pin last_name   first_name  suffix  middle_name status  pin pin pin pin tin first_name  last_name   middle_name suffix  tin first_name  last_name   middle_name suffix  tin first_name  last_name   middle_name suffix  tin first_name  last_name   middle_name suffix  effectivity_qtr effectivity_year    effectivity_qtr effectivity_year    effectivity_qtr effectivity_year    effectivity_qtr effectivity_year
1   015-08-0011-000-01  1st 2013    Active  1   342-432-423-000 015-08-0011-000-01  Smith   John    James   Jr  Active  1   015-08-0011-000-01-1001 015-08-0011-000-01  Active  1   015-08-0011-000-01-1001 1st 2013    Active  1   342-432-423-000 015-08-0011-000-01-1001 Smith   John    James   Jr  Active  1   015-08-0011-000-01-2001 015-08-0011-000-01  Active  1   015-08-0011-000-01-2001 Active  1st 2013    1   342-432-423-000 015-08-0011-000-01-2001 Smith   John    James   Jr  Active  1   015-08-0011-000-01  Active  1st 2013    1   342-432-423-000 015-08-0011-000-01  Smith   John    Jr  James   Active  015-08-0011-000-01  015-08-0011-000-01-1001 015-08-0011-000-01-2001 015-08-0011-000-01  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  1st 2013    1st 2013    1st 2013    1st 2013
2   015-08-0011-000-01  1st 2014    Active  1   342-432-423-000 015-08-0011-000-01  Smith   John    James   Jr  Active  1   015-08-0011-000-01-1001 015-08-0011-000-01  Active  1   015-08-0011-000-01-1001 1st 2014    Active  1   342-432-423-000 015-08-0011-000-01-1001 Smith   John    James   Jr  Active  1   015-08-0011-000-01-2001 015-08-0011-000-01  Active  1   015-08-0011-000-01-2001 Active  1st 2014    1   342-432-423-000 015-08-0011-000-01-2001 Smith   John    James   Jr  Active  1   015-08-0011-000-01  Active  1st 2014    1   342-432-423-000 015-08-0011-000-01  Smith   John    Jr  James   Active  015-08-0011-000-01  015-08-0011-000-01-1001 015-08-0011-000-01-2001 015-08-0011-000-01  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  1st 2014    1st 2014    1st 2014    1st 2014
3   015-08-0011-000-01  1st 2015    Active  1   342-432-423-000 015-08-0011-000-01  Smith   John    James   Jr  Active  1   015-08-0011-000-01-1001 015-08-0011-000-01  Active  1   015-08-0011-000-01-1001 1st 2015    Active  1   342-432-423-000 015-08-0011-000-01-1001 Smith   John    James   Jr  Active  1   015-08-0011-000-01-2001 015-08-0011-000-01  Active  1   015-08-0011-000-01-2001 Active  1st 2015    1   342-432-423-000 015-08-0011-000-01-2001 Smith   John    James   Jr  Active  1   015-08-0011-000-01  Active  1st 2015    1   342-432-423-000 015-08-0011-000-01  Smith   John    Jr  James   Active  015-08-0011-000-01  015-08-0011-000-01-1001 015-08-0011-000-01-2001 015-08-0011-000-01  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  342-432-423-000 John    Smith   James   Jr  1st 2015    1st 2015    1st 2015    1st 2015
 Record Count: 3; Execution Time: 8ms View Execution Plan  link
Did this query solve the problem? If so, consider donating $5 to help make sure SQL Fiddle will be here next time you need help with a database problem. Thanks!
SQL Sentry  CollectServer.info - Easy Server Health Monitoring

我发布的第一个预期输出一定造成了混淆，所以我决定再发布一个。这是直接来自 sqlfiddle，我只是调整了一些不正确的值。我想每年获取所有数据。我希望现在已经清楚了。

只要我每年可以获得所有数据，我就可以接受任何其他选择查询。需要考虑的是，某些表中的 pin 可以是多个，某些表可以是空的。

Answer 1

联接创建表的叉积。在您的情况下，有多行具有相同的 pin 值，因此连接的工作方式如下:

table1                          table2_                             table3__                  
                                                                                              
                                1. 015-08-0011-000-01-1001 +------> 1. 015-08-0011-000-01-1001
1. 015-08-0011-000-01-1001 +--> 2. 015|08|0011|000|01|1001+-----+   2. 015|08|0011|000|01|1001
                                3. 015-08-0011-000-01-1001 +-+  |   3. 015-08-0011-000-01-1001
                                                             |  |                             
                                1. 015-08-0011-000-01-1001   |  |   1. 015-08-0011-000-01-1001
2. 015-08-0011-000-01-1001 +--> 2. 015|08|0011|000|01|1001   |  +-> 2. 015|08|0011|000|01|1001
                                3. 015-08-0011-000-01-1001   |      3. 015-08-0011-000-01-1001
                                                             |                                
                                1. 015-08-0011-000-01-1001   |      1. 015-08-0011-000-01-1001
3. 015-08-0011-000-01-1001 +--> 2. 015|08|0011|000|01|1001   +----> 2. 015|08|0011|000|01|1001
                                3. 015-08-0011-000-01-1001          3. 015-08-0011-000-01-1001
                                                                                              
                                                                                              
                                                                                              
                                                                          ...                 

要在 table1 中为每个条目准确显示一行，请在查询末尾添加 GROUP BY t1.sysid。