Thread: Join issue?

Hi,

I have a problem with this join query:

<sql>
SELECTCASE 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>

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

What am I doing wrong?

Tables structure is:

CREATE TABLE companies
(  id_company int8 NOT NULL DEFAULT nextval('mgw__seq_cnt_companies'::text),  cmp_node int8[] NOT NULL DEFAULT
'{0}'::bigint[], cmp_name varchar(150) NOT NULL DEFAULT '???'::character varying,  cmp_created timestamp NOT NULL
DEFAULTnow(),  cmp_created_by int8 NOT NULL DEFAULT 0,  CONSTRAINT mgw_cnt_companies_pkey PRIMARY KEY (id_company,
cmp_node)
)
WITH OIDS;
ALTER TABLE mgw_cnt_companies OWNER TO postgres;

CREATE TABLE users_rights
(  id_rights int8 NOT NULL DEFAULT
nextval('mgw__seq_cnt_users_rights'::text),  r_category varchar(3) NOT NULL DEFAULT 'CMP'::character varying,
r_id_objectint8 NOT NULL DEFAULT 0,  r_id_user int8 NOT NULL DEFAULT 0,  r_created timestamp NOT NULL DEFAULT now(),
r_created_byint8 NOT NULL DEFAULT 0,  r_expires timestamp NOT NULL DEFAULT (now() + '365 days'::interval),  r_suspended
int2NOT NULL DEFAULT 0,  r_rights varchar(3) NOT NULL DEFAULT 'RWD'::character varying,  CONSTRAINT
mgw_cnt_users_rights_pkeyPRIMARY KEY (id_rights)
 
)
WITH OIDS;
ALTER TABLE mgw_cnt_users_rights OWNER TO postgres;

CREATE TABLE mgw_users
(  id int4 NOT NULL,  username varchar(100) NOT NULL,  "level" int4,  CONSTRAINT mgw_users_pkey PRIMARY KEY (id)
)
WITH OIDS;
ALTER TABLE mgw_users OWNER TO postgres;

Table data is:

INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (1, '{0}', 'A Company', '2004-12-13
18:04:11.288622', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (2, '{0,0}', 'A SubCompany',
'2004-12-13 18:04:31.612607', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (3, '{0,0,0}', 'A Sub Sub Company',
'2004-12-13 18:04:49.207465', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (5, '{1}', 'Netscape', '2004-12-13
18:31:12.783856', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (6, '{1,0}', 'Netscape Division',
'2004-12-13 18:31:23.243747', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (7, '{1,0,0}', 'Netscape Navigator',
'2004-12-13 18:31:57.840392', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (8, '{1,1,0}', 'Mozilla',
'2004-12-13 18:32:17.618974', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (9, '{1,2,0}', 'AOL', '2004-12-14
14:56:45.938362', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (10, '{1,1,0,0}', 'Mozilla.org',
'2004-12-14 14:57:07.246855', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (11, '{1,1,0,1}', 'Mozillazine',
'2004-12-14 14:57:22.314781', 0);
INSERT INTO mgw_cnt_companies (id_company, cmp_node, cmp_name,
cmp_created, cmp_created_by) VALUES (4, '{0,0,1}', 'A Sub Sub Company
2', '2004-12-13 18:26:02.966243', 0);


INSERT INTO mgw_cnt_users_rights (id_rights, r_category, r_id_object,
r_id_user, r_created, r_created_by, r_expires, r_suspended, r_rights)
VALUES (1, 'CMP', 8, 2, '2004-12-14 18:05:30.946643', 0, '2005-12-14
18:05:30.946643', 0, 'RWD');
INSERT INTO mgw_cnt_users_rights (id_rights, r_category, r_id_object,
r_id_user, r_created, r_created_by, r_expires, r_suspended, r_rights)
VALUES (2, 'CMP', 8, 3, '2004-12-14 18:25:13.277141', 0, '2005-12-14
18:25:13.277141', 0, 'RWD');
INSERT INTO mgw_cnt_users_rights (id_rights, r_category, r_id_object,
r_id_user, r_created, r_created_by, r_expires, r_suspended, r_rights)
VALUES (3, 'CMP', 7, 2, '2004-12-15 11:11:35.916306', 0, '2005-12-15
11:11:35.916306', 0, 'RWD');
INSERT INTO mgw_cnt_users_rights (id_rights, r_category, r_id_object,
r_id_user, r_created, r_created_by, r_expires, r_suspended, r_rights)
VALUES (4, 'CNT', 8, 2, '2004-12-15 11:49:48.007345', 0, '2005-12-15
11:49:48.007345', 0, 'RWD');


INSERT INTO mgw_users (id, username, "level") VALUES (1, 'admin', 9);
INSERT INTO mgw_users (id, username, "level") VALUES (2, 'user0', 9);
INSERT INTO mgw_users (id, username, "level") VALUES (3, 'user1', 9);
INSERT INTO mgw_users (id, username, "level") VALUES (4, 'user2', 9);

Thanks,
Marian

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.