PHP,MySQL – 从单个JOIN返回多个可识别的行?

前端之家收集整理的这篇文章主要介绍了PHP,MySQL – 从单个JOIN返回多个可识别的行?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有三张桌子,比如:
*table1*

id | val1 | val2 | val3 | val4 | val5
 1 |  ... |  ... |  ... |  ... |  ...
 2 |  ... |  ... |  ... |  ... |  ...
 3 |  ... |  ... |  ... |  ... |  ...
... etc

*table2*

id | som1 | som2 
 1 |  ... |  ... 
 2 |  ... |  ... 
 3 |  ... |  ... 
... etc

*table3*

id | col1 | col2 
 1 |  ... |  ... 
 2 |  ... |  ... 
 3 |  ... |  ... 
... etc

其中第三个表中的col2将始终包含来自第一个表的val3,val4或val5中的单个值.第二个表仅包含在完整性问题中,并不是问题.

我的查询如下:

$db = new PDO(...);
$stmt = $db->prepare("SELECT t2.som1 AS t2som1,t1.val1 AS t1v1,t1.val2 AS t1v2,t1.val3 AS t1v3,t1.val4 AS t1v4,t1.val5 AS t1v5,t3.col1 AS t3col1
                        FROM table1 t1
                          JOIN table2 t2 ON t1.val2 = t2.som2
                          JOIN table3 t3 ON t1.val3 = t3.col2
                        WHERE (t1.val4=:input OR t1.val5=:input) 
                        AND (t1.val1=1 OR t1.val1=2)");

$stmt->execute(array('input'=>$inputVal));
$result = $stmt->fetchAll();

这完全符合预期.

但!

我不仅需要t3col1用于t1.val3 = t3.col2.我还需要t1.val4 = t3.col2和t1.val5 = t3.col2

我可以构建这条线:

JOIN table3 t3 ON (t1.val3 = t3.col2 OR t1.val4 = t3.col2 OR t1.val5 = t3.col2)

但后来我不知道用哪个值来获取t3col1.它只返回一个值,而不是全部三个.

看我的问题!?

我想要

SELECT t2.som1 AS t2som1,...
        t3.col1 AS t3col1,// for condition t1.val3 = t3.col2
        t3.col1 AS t3col2,// for condition t1.val4 = t3.col2
        t3.col1 AS t3col3  // for condition t1.val5 = t3.col2

可以这样做吗?

澄清

如果val1,val2和val3都包含值,我想返回一行,其中包含来自table3的所有三个匹配.不是三个单独的行,除了匹配列之外都是相同的.

This SQLFiddle是我不想要的一个例子.请注意,除了t3col1列之外,所有内容都相同.这应该是一行,包含所有t3col1值.

/澄清

我估计我可以分三次加入桌子:

SELECT t2.som1 AS t2som1,...
        t3.col1 AS t3col1
        t4.col1 AS t3col2
        t5.col1 AS t3col3
FROM table1 t1
  JOIN table2 t2 ON t1.val2 = t2.som2
  JOIN table3 t3 ON t1.val3 = t3.col2 // for condition t1.val3 = t3.col2
  JOIN table3 t4 ON t1.val4 = t3.col2 // for condition t1.val4 = t3.col2
  JOIN table3 t5 ON t1.val5 = t3.col2 // for condition t1.val5 = t3.col2
WHERE (...) 
AND (...)

但是这样做会失去性能(即4个JOINS),而不是我试图这样做,只有2个?

奖金!

有时val3,val4和/或val5将为空(但col2永远不会为空).对于那些空值的情况,所有table3 JOIN都需要LEFT JOIN吗?

编辑1

根据您的要求,我已经在sqlFiddle中进行了设置,但该服务非常突破.

你可以找到它here

编辑2

我尝试多次加入table3:

SELECT t2.som1 AS t2som1,...
        t3.col1 AS t3col1
        t4.col1 AS t3col2
        t5.col1 AS t3col3
FROM table1 t1
  JOIN table2 t2 ON t1.val2 = t2.som2
  JOIN table3 t3 ON t1.val3 = t3.col2 // for condition t1.val3 = t3.col2
  JOIN table3 t4 ON t1.val4 = t3.col2 // for condition t1.val4 = t3.col2
  JOIN table3 t5 ON t1.val5 = t3.col2 // for condition t1.val5 = t3.col2
WHERE (...) 
AND (...)

并最终获得每场比赛的一排.我应该有7个结果,最终获得超过90个.

val1将始终具有值,并且每次填充val2和val3是否不同.但是当这三个都有价值时,我不想要三个单独的回报.相反,我只想要一个包含所有三个匹配的回归.

编辑4(澄清是3 …)

由于Siraj的回答,EDIT 2中的多个JOIN最终正在工作.问题是我用过:

JOIN table3 t3 ON t1.val3 = t3.col2 
JOIN table3 t4 ON t1.val4 = t3.col2 
JOIN table3 t5 ON t1.val5 = t3.col2

当我应该使用时:

JOIN table3 t3 ON t1.val3 = t3.col2 
JOIN table3 t4 ON t1.val4 = t4.col2 
JOIN table3 t5 ON t1.val5 = t5.col2

请注意区别?在每个等号后面只是t3.col2 – 他们需要引用正确的表格!其余的都保持不变,鲍勃是你的阿姨!

下面的查询将为您提供所需的数据:
SELECT t2.som1 AS t2som1,t3.col1 AS t3col1,t3.col2 AS t3col2,CASE WHEN t1.val3 = t3.col2 THEN t3.col1 ELSE '' END AS 't3col11',CASE WHEN t1.val4 = t3.col2 THEN t3.col1 ELSE '' END AS 't3col21',CASE WHEN t1.val5 = t3.col2 THEN t3.col1 ELSE '' END AS 't3col31',CASE WHEN t1.val3 = t3.col2 THEN 't1col3'
 ELSE CASE WHEN t1.val4 = t3.col2 THEN 't1col4' ELSE 't1col5'
 END END AS ColumnName
FROM table1 t1
  JOIN table2 t2 ON t1.val2 = t2.som2
  JOIN table3 t3 ON (t1.val3 = t3.col2 OR t1.val4 = t3.col2 OR t1.val5 = t3.col2)
GROUP BY t2som1,t1v1,t1v2,t1v3,t1v4,t1v5

最后一列是’ColumnName’,它告诉您哪个t1列是与该值匹配的列.

编辑 – 使用左连接

SELECT t2.som1 AS t2som1,t4.col1 AS t3col2,t5.col1 AS t3col3
FROM table1 AS t1
LEFT JOIN table2 t2 ON t1.val2 = t2.som2
LEFT JOIN table3 t3 ON t1.val3 = t3.col2
LEFT JOIN table3 t4 ON t1.val4 = t4.col2
LEFT JOIN table3 t5 ON t1.val5 = t5.col2
原文链接:/php/133936.html

猜你在找的PHP相关文章