Re: Join issue?

On Wed, 15 Dec 2004, Marian POPESCU wrote:

> Hi,
>
> I have a problem with this join query:
>
> <sql>
> SELECT
> CASE WHEN (UR.id_rights IS NULL) THEN '0' ELSE UR.id_rights END as
> id_rights,
> CASE WHEN (UR.r_category IS NULL) THEN 'CMP' ELSE UR.r_category END as
> category,
> U.id as id_user,
> U.username
> FROM mgw_users U LEFT OUTER JOIN mgw_cnt_users_rights UR ON (U.id =
> UR.r_id_user)
> WHERE (U."level" = 9)
> AND (
> ((UR.r_id_object = 5) OR (UR.r_id_object IS NULL))
> AND
> ((UR.r_category = 'CMP') OR (UR.r_category IS NULL))
> )
> ORDER BY U.username;
> </sql>
>
> I get this result and I expect something else:
> <result>
> 0;"CMP";1;"admin"
> 0;"CMP";4;"user2"
> </result>

Which appears to me to be correct for the above on the data you gave.
The outer join results in a set like:
id | r_id_object | r_category
----+-------------+------------
1 | |
2 | 8 | CMP
2 | 7 | CMP
2 | 8 | CNT
3 | 8 | CMP
4 | |
Which then is filtered by the where clause. All the id=2 and id=3 rows
fail the filter. Outer joins do not provide a NULL extended row if the
join condition succeeds on some rows.

> I would like to obtain
> <result>
> 0;"CMP";1;"admin"
> 0;"CMP";2;"user0"
> 0;"CMP";3;"user1"
> 0;"CMP";4;"user2"
> </result>

I'm not sure exactly what you want actually. The case when on r_category
seems redundant since you're asking for only rows that have 'CMP' or NULL
and are making the latter into the former.

In general, I think you need to consider moving some of your conditions on
UR into the ON clause like ON (U.id = UR.r_id_user and ur.r_id_object=5
...) in which case rows in UR that fail the extra conditions don't prevent
a NULL extending row from being produced.