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