Message-ID: <41C053D1.5060508@libertysurf.fr>
Date: Wed, 15 Dec 2004 16:10:09 +0100
From: Marian POPESCU <softexpert@libertysurf.fr>
Reply-To: softexpert@libertysurf.fr
User-Agent: Mozilla Thunderbird 1.0 (X11/20041208)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Join issue?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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>

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 DEFAULT now(),
   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_object int8 NOT NULL DEFAULT 0,
   r_id_user int8 NOT NULL DEFAULT 0,
   r_created timestamp NOT NULL DEFAULT now(),
   r_created_by int8 NOT NULL DEFAULT 0,
   r_expires timestamp NOT NULL DEFAULT (now() + '365 days'::interval),
   r_suspended int2 NOT NULL DEFAULT 0,
   r_rights varchar(3) NOT NULL DEFAULT 'RWD'::character varying,
   CONSTRAINT mgw_cnt_users_rights_pkey PRIMARY 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