*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