CODEWITHSUNDEEP
mysql
Franco
Franco

Reputation: 2926

Where value doesnt exist in joined table

I'm using this query to find if an email address from users doesn't exist in the emails_sent table.

SELECT users.email,users.registration_date 
FROM users 
LEFT JOIN emails_sent
ON emails_sent.email=users.email
WHERE emails_sent.email IS NULL
ORDER BY registration_date DESC
LIMIT 0,50

Instead, I would like to return users.email where there is no record where emails_sent.type='welcome'

I tried the below but it returned no results

SELECT users.email,users.registration_date 
FROM users 
LEFT JOIN emails_sent
ON emails_sent.email=users.email
WHERE emails_sent.type!='welcome'
ORDER BY registration_date DESC
LIMIT 0,50

emails_sent table is as follows

id  int(11) unsigned    NO  PRI NULL
email   varchar(200)    YES     NULL
type    varchar(200)    YES     NULL
timestamp   int(200)    YES     NULL

Upvotes: 0

Views: 18

Answers (1)

DRapp
DRapp

Reputation: 48139

just include the "AND" condition to the LEFT-JOIN portion, not the WHERE

SELECT 
      users.email,
      users.registration_date 
   FROM 
      users 
         LEFT JOIN emails_sent
            ON users.email = emails_sent.email
           AND emails_sent.type = 'welcome'
   where
       emails_sent.email IS NULL
   ORDER BY 
      registration_date DESC
   LIMIT 
      0,50

So your join is on both criteria of matching user and SPECIFICALLY the "welcome" email message. o

Upvotes: 2

Related Questions