CODEWITHSUNDEEP
mysqlsql
user765368
user765368

Reputation: 20356

join sql query based on value of another table (in one query)

Let's say that I have two tables A and B where

A is table countries with columns id, name, created, modified

that contains a bunch of countries

And B is table users with columns id, first_name, last_name, email, country_id, created, modified

that contains a bunch of users linked to countries via foreign key country_id

What is the most efficient query to get all the countries that don't have a user with email address "[email protected]" associated to it?

I tried something like the following but that didn't work:

SELECT DISTINCT
    c.*
FROM
    countries c
LEFT JOIN
    users u ON u.country_id = c.id
WHERE
    u.email <> '[email protected]'

Thanks for any help

NOTE I also tried putting the condition on the email column in the ON clause that didn't work either

Upvotes: 0

Views: 80

Answers (2)

jwolf
jwolf

Reputation: 938

When you say "that don't have a user with email address "[email protected]"", do you mean no email address -or- not that exact email address?

Updated

Then this should do:

SELECT DISTINCT c.*
FROM countries c
LEFT JOIN users u ON u.country_id = c.id and u.email = '[email protected]'
WHERE u.country_id is null

Which I believe is what Gordon already had.

Updated Again

In that case, try:

SELECT DISTINCT c.*
FROM countries c
INNER JOIN users u ON u.country_id = c.id and ISNULL(u.email, '') = ''

This looks for Null or Empty String email adresses all others are excluded from the join and therefore from the result set.

I hope this helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

A left join is fine, you just need to set it up correctly:

SELECT c.*
FROM countries c LEFT JOIN
     users u
     ON u.country_id = c.id AND u.email = '[email protected]'
WHERE u.country_id IS NULL;

In terms of performance, this should be pretty similar to NOT EXISTS and NOT IN (although I do not recommend the latter because it has different behavior when there are NULL values).

Upvotes: 3

Related Questions