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

Question

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

Gordon Linoff · Accepted Answer · 2018-01-23 22:18:18Z

3

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

answered Jan 23, 2018 at 22:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jwolf · Accepted Answer · 2018-01-23 22:41:48Z

0

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.

edited Jan 23, 2018 at 22:41

answered Jan 23, 2018 at 22:19

jwolf

9388 silver badges13 bronze badges

3 Comments

jwolf Over a year ago

Yep, same as @Gordon

jwolf Over a year ago

My bad, you said: NOT that exact address - New Solution on the way.

jwolf Over a year ago

@user765368 , One of the two queries above should be the one. But I'm still not sure about your question. It sounds like you're saying (and Gordon interpreted this way too) that everybody in every country is supposed to have the same email address - show me a list of countries where there is at least one user who doesn't have the same address as the rest of the world. Is that correct?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related