MySQL左外部联接验证？
MySQL左外部联接验证？
提问于 2014-05-01 12:33:11
我有一个表，列出了位置和每个位置的可接受字段：
地点、地点和可接受的字段
----------------------------------------------------------|
Location_A *地址
Location_A手机、手机、手机
Location_A在几个小时内完成了几个小时。
Location\_B Address Location_B手机、手机、手机
现在，我有一个表，列出了我想要针对第一个表进行验证的值。
ID :地点:地址；地址:地址:电话：
----------------------------------------------------------------------|
1-地点A- 123部分圣路易斯9999999 - 8-5
2、地点B: 456，部分圣路易斯9999998；8-4
现在，位置A通过了小时数验证，但是位置B列出了小时数，但根据验证表，它不应该列出。这应该是NULL。我希望我的查询返回位置B，8-4。下面是我的MySQL语句，它显然不起作用：
SELECT Table2.Location, Table2.Hours
FROM
Table1 LEFT OUTER JOIN Table2
ON
Table1.Location = Table2.Location
WHERE
Table2.Hours is not null
AND Table1.Acceptable_Fields != 'Hours';回答 2
Stack Overflow用户
发布于 2014-05-01 13:20:41
这个怎么样？
SELECT Table1.Location, Table1.Acceptable_Fields
FROM Table1 RIGHT JOIN
(
SELECT Location, 'Address' AS col_name, Address AS col_val
FROM Table2
UNION ALL
SELECT Location, "Phone" AS col_name, Phone AS col_val
FROM Table2
UNION ALL
SELECT Location, "Hours" AS col_name, Hours AS col_val
FROM Table2
) T3 ON Table1.Location = Table3.Location
AND Table3.col_name = Table1.Acceptable_Fields
WHERE Table3.col_name IS NULL AND TAble3.col_val IS NOT NULL;Stack Overflow用户
发布于 2014-05-01 13:27:58
有两种方法可以实现这一点：
使用左连接。您将需要向左连接控制表三次(每个控制参数(即可接受字段)一次)，并检查是否为NULL，如下所示：
SELECT t2.ID,
t2.Location,
CASE WHEN a.Acceptable_Fields IS NOT NULL THEN t2.Address END Address,
CASE WHEN p.Acceptable_Fields IS NOT NULL THEN t2.Phone END Phone,
CASE WHEN h.Acceptable_Fields IS NOT NULL THEN t2.Hours END Hours
FROM
Table2 t2
LEFT OUTER JOIN Table1 a ON
t2.Location = a.Location AND a.Acceptable_Fields = 'Address'
LEFT OUTER JOIN Table1 p ON
t2.Location = p.Location AND p.Acceptable_Fields = 'Phone'
LEFT OUTER JOIN Table1 h ON
t2.Location = h.Location AND h.Acceptable_Fields = 'Hours'使用子查询：
SELECT t2.ID,
t2.Location,
CASE WHEN EXISTS(SELECT 1 FROM Table1 WHERE Location = t2.Location AND Acceptable_Fields = 'Address') THEN t2.Address END Address,
CASE WHEN EXISTS(SELECT 1 FROM Table1 WHERE Location = t2.Location AND Acceptable_Fields = 'Phone') THEN t2.Phone END Phone,
CASE WHEN EXISTS(SELECT 1 FROM Table1 WHERE Location = t2.Location AND Acceptable_Fields = 'Hours') THEN t2.Hours END Hours
FROM
Table2 t2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23402040
复制相关文章
相似问题
腾讯云开发者
