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?

+1


source to share


6 answers


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).

+4


source


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.

+2


source


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'

      

+1


source


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'

      

0


source


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.

0


source


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 ...

0


source







All Articles