From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Marian POPESCU <softexpert@libertysurf.fr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue?
Date: Wed, 15 Dec 2004 07:46:01 -0800 (PST)
Message-ID: <20041215073608.J44037@megazone.bigpanda.com> (raw)
In-Reply-To: <41C053D1.5060508@libertysurf.fr>
References: <41C053D1.5060508@libertysurf.fr>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, softexpert@libertysurf.fr
Subject: Re: Join issue?
In-Reply-To: <20041215073608.J44037@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox