使用INNER JOIN返回设置表中多个值匹配的记录
使用INNER JOIN返回设置表中多个值匹配的记录
提问于 2021-09-28 09:57:11
我有一个仓库表(wms)和一个设置表(wms_setting)。
我正在尝试从wms返回仓库列表，其中在设置表中，仓库被标记为活动的，并且dispatch_notification_method = pull。
显然，下面的查询不会返回任何内容，因为所有4个条件必须在一行上匹配，这是不可能的。
SELECT *
FROM wms
INNER JOIN wms_setting
ON wms.wms_id = wms_setting.wms_wms_id
WHERE wms_setting.name = 'active'
AND wms_setting.value_int = '1'
AND wms_setting.name = 'dispatch_notification_method'
AND wms_setting.value_str = 'pull' 这可以在一个查询中完成吗？或者我应该先检查一个设置，比如'active‘，然后再进行第二次查询来检查dispatch_notification_method的值？
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-09-28 10:10:02
您可以使用子查询来检查您的设置，或者只使用两个连接。
SELECT *
FROM wms
INNER JOIN wms_setting ws ON wms.wms_id = ws.wms_wms_id AND ws.name = 'active' AND ws.value_int = '1'
INNER JOIN wms_setting ws2 ON wms2.wms_id = ws2.wms_wms_id AND ws2.name = 'dispatch_notification_method' AND ws2.value_str = 'pull'子查询：
SELECT *
FROM wms
WHERE
EXISTS (SELECT ws.id wms_setting ws WHERE wms.wms_id = ws.wms_wms_id
AND ws.name = 'active' AND ws.value_int = '1')
AND
EXISTS (SELECT ws2.id wms_setting ws2 WHERE wms.wms_id = ws2.wms_wms_id
AND ws2.name = 'dispatch_notification_method' AND ws2.value_str = 'pull')Stack Overflow用户
发布于 2021-09-28 10:14:23
SELECT wms.name /* , another columns from wms */
FROM wms
INNER JOIN wms_setting ON wms.wms_id = wms_setting.wms_wms_id
WHERE (wms_setting.name = 'active' AND wms_setting.value_int = '1')
OR (wms_setting.name = 'dispatch_notification_method' AND wms_setting.value_str = 'pull')
GROUP BY wms.name /* , another columns from wms */
HAVING COUNT( /* DISTINCT */ wms_setting.name) = 2;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69359605
复制相关文章
相似问题
腾讯云开发者
