pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Marian POPESCU <softexpert@libertysurf.fr>
To: pgsql-sql@postgresql.org
Subject: Join issue?
Date: Wed, 15 Dec 2004 16:10:09 +0100
Message-ID: <41C053D1.5060508@libertysurf.fr> (raw )
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
view thread (10+ messages) latest in thread
Message-ID: <41C053D1.5060508@libertysurf.fr>
Permalink: ../41C053D1.5060508@libertysurf.fr/
Also on: postgresql.org/message-id/41C053D1.5060508@libertysurf.fr
copy link · copy postgr.es
reply 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: softexpert@libertysurf.fr
Subject: Re: Join issue?
In-Reply-To: <41C053D1.5060508@libertysurf.fr>
* 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