CODEWITHSUNDEEP
mysqlsqlselect
Markie Mark
Markie Mark

Reputation: 129

Get Records Not Present in Other Table

I have two tables:

all_countries

╔════╦═════════════╗
║ ID ║   COUNTRY   ║
╠════╬═════════════╣
║  1 ║ USA         ║
║  2 ║ China       ║
║  3 ║ India       ║
║  4 ║ France      ║
║  5 ║ UK          ║
║  6 ║ Australia   ║
║  7 ║ Philippines ║
╚════╩═════════════╝

supported_countries

╔════════════╦═══════════╦═══════════╗
║ COUNTRY_ID ║  COUNTRY  ║ FILTER_ID ║
╠════════════╬═══════════╬═══════════╣
║          1 ║ USA       ║         1 ║
║          2 ║ China     ║         1 ║
║          3 ║ India     ║         1 ║
║          4 ║ France    ║         2 ║
║          1 ║ USA       ║         2 ║
║          6 ║ Australia ║         2 ║
╚════════════╩═══════════╩═══════════╝

Query:

SELECT a.* 
FROM all_countries a
LEFT JOIN supported_countries s
  ON a.id = s.country_id
WHERE s.country_id IS NULL;

So as per above example I should get.

UK
Philippines

It works fine, but what if, if I filter the country example I choose to filter = 2. i get zero result.

SELECT a.* 
FROM all_countries a
LEFT JOIN supported_countries s
  ON a.id = s.country_id
WHERE s.filter = 2 AND s.country_id IS NULL;

supposed to be i want to get this result, if filter = 2.

╔════╦═════════════╗
║ ID ║   COUNTRY   ║
╠════╬═════════════╣
║  2 ║ China       ║
║  3 ║ India       ║
║  5 ║ UK          ║
║  7 ║ Philippines ║
╚════╩═════════════╝

Am I doing the correct query?

Upvotes: 1

Views: 437

Answers (1)

John Woo
John Woo

Reputation: 263883

add the condition on the ON clause,

SELECT  a.* 
FROM    all_countries a
        LEFT JOIN supported_countries s
           ON a.id = s.country_id AND 
              s.filter_ID = 2        -- <<=== HERE
WHERE   s.country_id IS NULL;

Result:

╔════╦═════════════╗
║ ID ║   COUNTRY   ║
╠════╬═════════════╣
║  2 ║ China       ║
║  3 ║ India       ║
║  5 ║ UK          ║
║  7 ║ Philippines ║
╚════╩═════════════╝

Upvotes: 2

Related Questions