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?
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).
If the string does not exist, the credit_acc.type_acc value must be null. You can try something like this:
WHERE credit_acc.type_acc = 'init' OR credit_acc.type_acc IS NULL;
This will only work if there are no null fields in credit_acc.type_acc.
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'
Do you want all records from the two tables to be merged into a user id WHERE credit_acc is "init" OR where is there no credit_acc row to be merged? What about
where credit_acc.type_acc is null OR credit_acc.type_acc = 'init'
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.
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 ...