Query join question
I have a right outer join that almost does what I want ...
SELECT
users_usr.firstname_usr,
users_usr.lastname_usr,
credit_acc.given_credit_acc,
users_usr.created_usr,
users_usr.sitenum_usr,
users_usr.original_aff_usr,
users_usr.id_usr
FROM
credit_acc
right Outer Join users_usr ON credit_acc.uid_usr = users_usr.id_usr
The problem is that I want to add
where credit_acc.type_acc = 'init'
But that gets rid of all users who don't have a line in credit_acc ... which is WHY I need the right outer join.
Is there a way to get this without having to do two queries and a union?
source to share
SELECT
users_usr.firstname_usr,
users_usr.lastname_usr,
credit_acc.given_credit_acc,
users_usr.created_usr,
users_usr.sitenum_usr,
users_usr.original_aff_usr,
users_usr.id_usr
FROM
credit_acc
right Outer Join users_usr ON credit_acc.uid_usr = users_usr.id_usr
WHERE credit_acc.type_acc = 'init' OR credit_acc.type_acc is NULL
Or, as @ Tomalak pointed out:
WHERE COALESCE(credit_acc.type_acc, 'init') = 'init'
which could be faster (see comments).
source to share
Have you tried the following?
SELECT
users_usr.firstname_usr,
users_usr.lastname_usr,
credit_acc.given_credit_acc,
users_usr.created_usr,
users_usr.sitenum_usr,
users_usr.original_aff_usr,
users_usr.id_usr
FROM
credit_acc
right Outer Join users_usr ON credit_acc.uid_usr = users_usr.id_usr and credit_acc.type_acc = 'init'
source to share
I would make a predicate:
WHERE credit_acc.uid_usr IS NULL OR credit_acc.type_acc = 'init'
This will give you lines where there is no match in UID_USR, but lines where there is a match and the account type is "init".
Another suggested solution (checking type_acc for NULL) will also give you rows where there is a match for UID_USR and the actual value for account type is NULL.
If credit_acc.type_acc cannot be NULL, there is no difference between the two. If possible, you need to decide if you want to include these rows in your result set.
source to share
Just add another predicate to the join condition.
SELECT U.firstname_usr, U.lastname_usr, C.given_credit_acc,
U.created_usr, U.sitenum_usr, U.original_aff_usr, U.id_usr
From credit_acc C Right Join users_usr U
On C.uid_usr = U.id_usr
And C.type_acc = 'init'
This works because the join conditions are applied before inconsistent records from the "other" side of the "Outer" join are added to the result set, whereas the "Conditions" are applied after the two tables are joined ...
This syntax reflects your intent more clearly ...
source to share