CODEWITHSUNDEEP
sqlpostgresql
Curtis
Curtis

Reputation: 55

PostgreSQL - Need a way to return all results

Currently, this query only returns cases where there is a value in my "SFDC Contact ID" column. Curious how I can change the query to return all results? I assume this would need to be an outer join?

SELECT cmsuser.userid as "User ID", cmsuser.username as "Username", cmsuserprofile.value as "SFDC Contact ID"
FROM cmsuser, cmsuserprofile 
WHERE cmsuser.userid = cmsuserprofile.userid and fieldid = '5004';

Upvotes: 1

Views: 95

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Presumably, fieldid is in the profile table. And, by "all results" you mean "all users". If so, then you do want a left join like this:

SELECT u.userid as "User ID", u.username as "Username",
       p.value as "SFDC Contact ID"
FROM cmsuser u LEFT JOIN
     cmsuserprofile p
     ON u.userid = p.userid and p.fieldid = '5004';

Note: you should use proper explicit join syntax. A simple rule: never use commas in the from clause.

Upvotes: 0

mucio
mucio

Reputation: 7119

You need something like:

         SELECT cmsuser.userid as "User ID", 
                cmsuser.username as "Username", 
                cmsuserprofile.value as "SFDC Contact ID"
           FROM cmsuser
LEFT OUTER JOIN cmsuserprofile 
             ON cmsuser.userid = cmsuserprofile.userid 
           WHERE fieldid = '5004';

The LEFT JOIN will return all records from the cmsuser (the table on the left side of the join) even if there is no match on the cmsuserprofile.

It's a good practice to separate JOIN and WHERE conditions to make your code more easy to understand and maintain, in this way it's easier to get where you have a join condition and what is just a where clause.

Upvotes: 2

Related Questions