CODEWITHSUNDEEP
sqlpostgresql
Dan Rubio
Dan Rubio

Reputation: 4887

Can someone help me point out what is incorrect about this double LEFT OUTER JOIN query?

I am trying to count the amount of accounts that currently do not possess a "position" or a "verification". In order to do this I need to perform two left outer joins on both the verifications and positions table. This is the query that I have constructed so far:

SELECT COUNT(*)
FROM
    "accounts"
    LEFT OUTER JOIN
    "verifications" ON "accounts.id = verifications.account_id" 
    LEFT OUTER JOIN
    "positions" ON "accounts.id = positions.account_id"
WHERE "verifications.account_id = null" AND "positions.account_id = null";

This seems to not be the correct query. My console produces this error:

SyntaxError: unexpected tCONSTANT, expecting end-of-input
SELECT * FROM "accounts" LEFT OUTER JOIN "verifications"

It looks like there is a problem at the beginning of the statement near the accounts. I've already checked out some tutorials on double and triple left outer joins and I'm linking onto the correct fields. I'm at a loss as to what could be wrong. Help would be greatly appreciated. Thank you.

Upvotes: 0

Views: 67

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

If you want or need to use double quotes do:

ON "accounts"."id" = "verifications"."account_id"

The double quotes wraps each identifier. Using double quotes is a bad ideia unless there are illegal characters in the identifier (also not that good)

Upvotes: 2

Iłya Bursov
Iłya Bursov

Reputation: 24146

write your query like this:

SELECT COUNT(*)
FROM accounts
LEFT OUTER JOIN verifications
ON accounts.id = verifications.account_id
LEFT OUTER JOIN positions
ON accounts.id = positions.account_id
WHERE verifications.account_id is null AND positions.account_id is null;

Upvotes: 1

Related Questions