Dan Rubio Dan Rubio - 24 days ago 8
SQL Question

Why can I not combine a LEFT OUTER JOIN and WHERE clause in this query?

I am running into a syntax error with the following SQL query:

SELECT COUNT(*)
from accounts
WHERE accounts.level = 0
LEFT OUTER JOIN verifications ON verifications.account_id = accounts.id
WHERE verifications.account_id IS NULL;


Here is a second variation that I have tried:

SELECT COUNT(*)
from accounts
LEFT OUTER JOIN verifications ON verifications.account_id = accounts.id
WHERE verifications.account_id IS NULL
WHERE accounts.level = 0;


What I want to do is I want to select
accounts
that do not have an associated
verifications
object and whose level is equal only to 0. In my mind this query makes sense to me but obviously not. What could I be doing wrong here? Additionally, as a bonus, I intend to add more
LEFT OUTER JOINS
statements to filter the accounts table with the combined associations. For this scenario would a
UNION
statement be needed to aggregate the results or can I just pile on the
LEFT OUTER JOIN
? Help would be appreciated.

Answer

What I want to do is I want to select accounts that do not have an associated verifications object and whose level is equal only to 0.

A more canonical way to do that is with NOT EXISTS:

SELECT COUNT(*)
from accounts
WHERE accounts.level = 0 
AND NOT EXISTS(
   SELECT NULL 
   FROM verifications 
   WHERE verifications.account_id = accounts.id)