query join question

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, I want to add a

where credit_acc.type_acc = 'init'

But this gets rid of all users who don't have a row in credit_acc... which is WHY I need a right outer join.

Is there a way to get this without having to do two queries and a union?

Answer 1

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 may be faster (see comments).

Answer 2

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'

Answer 3

If the row doesn't exist, *credit_acc.type_acc* should be null. You could try something like this:

WHERE credit_acc.type_acc = 'init' OR credit_acc.type_acc IS NULL;

That will only work if there are no null fields in *credit_acc.type_acc*.

Answer 4

You want all records from the two tables, joined on user-id, WHERE credit_acc is 'init' OR where there isn't a credit_acc row to be joined? How about

where credit_acc.type_acc is null  OR credit_acc.type_acc = 'init'

Answer 5

I would make the predicate:

WHERE credit_acc.uid_usr IS NULL OR credit_acc.type_acc = 'init'

This would give you rows where there is no match on UID_USR, and rows where there is a match and the account type is 'init'.

The other solution proposed (checking type_acc for NULL) would also give you rows where there is a match on UID_USR and the actual value for account type is NULL.

If credit_acc.type_acc can't be NULL, there's no difference between the two. If it can, you need to decide whether you want to include those rows in your result set.

Answer 6

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 Join conditions are applied BEFORE the non-matching records from "other" side of an "Outer" join are added to the result set, whereas Where Conditions are applied after the two tables are joined...

This syntax more clearly represents your intent as well...