Using INNER JOIN to return records where multiple values match on a settings table-CodePudding

I have a table of warehouses (wms), and a settings table (wms_setting).

I am trying to return a list of warehouses from wms where in the settings table, the warehouse is marked as active, and dispatch_notification_method = pull.

Obviously the below query won't return anything because all 4 conditions must match on a single row, which isn't possible.

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'

Is this possible to do in a single query? Or should I check one setting such as 'active' first, then a second query to check the dispatch_notification_method value?

CodePudding user response：

You can use sub queries to check your settings, or just use two joins.

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'

Sub queries:

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')

CodePudding user response：

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;