pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoin issue?
10+ messages / 7 participants
[nested] [flat]
* Join issue?
@ 2004-12-15 15:10 Marian POPESCU <softexpert@libertysurf.fr>
0 siblings, 1 reply; 10+ messages in thread
From: Marian POPESCU @ 2004-12-15 15:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Join issue?
@ 2004-12-15 15:21 Marian POPESCU <softexpert@libertysurf.fr>
0 siblings, 0 replies; 10+ messages in thread
From: Marian POPESCU @ 2004-12-15 15:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue?
@ 2004-12-15 15:46 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Marian POPESCU <softexpert@libertysurf.fr>
0 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2004-12-15 15:46 UTC (permalink / raw)
To: Marian POPESCU <softexpert@libertysurf.fr>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Join issue
@ 2006-06-06 18:10 Burak Seydioglu <buraks78@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Burak Seydioglu @ 2006-06-06 18:10 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have one to many (from left to right) relationships among the below tables
in my database
user -> house -> contract -> contract status
|
Also, a single house has a single provider and the provider has multiple
rates inside the provider_rate table in chronological order.
I have a query to return the latest contract and contract status for a
house... What i am trying to do is to get the rate of electricity for the
latest contract... I am trying to retrieve the latest provider rate before a
contract is signed.
(Please see section marked with >> below).
Here is the latest version of the SQL and it does not work (see
ce_contract.contract_created)
SELECT
ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_status
FROM ce_house
LEFT JOIN ce_provider_rate ON
ce_provider_rate.provider_id=ce_house.provider_id
LEFT JOIN ce_contract ON ce_house.house_id=ce_contract.house_id
LEFT JOIN ce_contract_status ON
ce_contract.contract_id=ce_contract_status.contract_id
WHERE
ce_contract.contract_id IN (SELECT MAX(ce_contract.contract_id) FROM
ce_contract GROUP BY ce_contract.house_id)
AND
ce_contract_status.contract_status_id IN (SELECT
MAX(ce_contract_status.contract_status_id) FROM ce_contract_status GROUP BY
ce_contract_status.contract_id)
AND
>> ce_provider_rate.provider_rate_id IN (SELECT
MAX(ce_provider_rate.provider_rate_id) FROM ce_provider_rate WHERE
ce_provider_rate.provider_rate_created<=ce_contract.contract_created)
AND
ce_house.house_id='1'
I would appreciate any insight to help me solve this issue...
Burak
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue
@ 2006-06-06 18:27 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Burak Seydioglu <buraks78@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Andrew Sullivan @ 2006-06-06 18:27 UTC (permalink / raw)
To: pgsql-sql
On Tue, Jun 06, 2006 at 11:10:46AM -0700, Burak Seydioglu wrote:
> I have a query to return the latest contract and contract status for a
> house... What i am trying to do is to get the rate of electricity for the
> latest contract... I am trying to retrieve the latest provider rate before a
> contract is signed.
I don't have a proposal to rewrite, but I suspec this is going to be
easier with a query to get the latest provider rate in the FROM
clause.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue
@ 2006-06-06 22:33 operationsengineer1@yahoo.com
parent: Burak Seydioglu <buraks78@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-06-06 22:33 UTC (permalink / raw)
To: Burak Seydioglu <buraks78@gmail.com>; pgsql-sql
> Hi,
>
> I have one to many (from left to right)
> relationships among the below tables
> in my database
>
> user -> house -> contract -> contract status
> |
>
> Also, a single house has a single provider and the
> provider has multiple
> rates inside the provider_rate table in
> chronological order.
>
> I have a query to return the latest contract and
> contract status for a
> house... What i am trying to do is to get the rate
> of electricity for the
> latest contract... I am trying to retrieve the
> latest provider rate before a
> contract is signed.
>
> (Please see section marked with >> below).
>
> Here is the latest version of the SQL and it does
> not work (see
> ce_contract.contract_created)
>
> SELECT
>
ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_status
> FROM ce_house
> LEFT JOIN ce_provider_rate ON
> ce_provider_rate.provider_id=ce_house.provider_id
> LEFT JOIN ce_contract ON
> ce_house.house_id=ce_contract.house_id
> LEFT JOIN ce_contract_status ON
>
ce_contract.contract_id=ce_contract_status.contract_id
> WHERE
> ce_contract.contract_id IN (SELECT
> MAX(ce_contract.contract_id) FROM
> ce_contract GROUP BY ce_contract.house_id)
> AND
> ce_contract_status.contract_status_id IN (SELECT
> MAX(ce_contract_status.contract_status_id) FROM
> ce_contract_status GROUP BY
> ce_contract_status.contract_id)
> AND
> >> ce_provider_rate.provider_rate_id IN (SELECT
> MAX(ce_provider_rate.provider_rate_id) FROM
> ce_provider_rate WHERE
>
ce_provider_rate.provider_rate_created<=ce_contract.contract_created)
> AND
> ce_house.house_id='1'
>
>
> I would appreciate any insight to help me solve this
> issue...
Burak, having battled a couple big queries over that
last week, my first piece of advice is simplify the
query to the area that is giving you the trouble...
iow, set up your query such that it *only* pulls the
latest contract rate and leave all the noise out (you
already know how to do the noise and can add it back
in later).
the query you want seems very similar to a subquery i
recently put together.
instead of the latest date contract rate by house, i
needed the latest value of a boolean by inspection
node.
a simplified version of the query i used is here...
http://www.rafb.net/paste/results/m322aH47.html
of course, you have to adjust for different table
relations.
the query, as it stands, lists *all* latest result
grouped by inspection_id.
you'll be adding...
AND ce_house.house_id='1' and add associated FROM
tables and WHERE equations.
you can specify
AND ce_house.house_id='1'
to limit the results further.
this is my best try at the partial query given i can't
totally understand your table structure...
http://www.rafb.net/paste/results/MlJUrO80.html
if i guessed wrong, adjust for your actual table
structure.
good luck.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue
@ 2006-06-06 23:53 Kenneth B Hill <ken@scottshill.com>
parent: operationsengineer1@yahoo.com
0 siblings, 1 reply; 10+ messages in thread
From: Kenneth B Hill @ 2006-06-06 23:53 UTC (permalink / raw)
To: operationsengineer1@yahoo.com; +Cc: Burak Seydioglu <buraks78@gmail.com>; pgsql-sql
That looks like a very complex query. I would like to suggest that you
try doing some nesting queries via a SQL script. Make a "view" with a
query, then perform a query using the view, etc. , then drop all views
in the SQL script. This may make the entire operation perform faster.
-Ken
On Tue, 2006-06-06 at 15:33 -0700, operationsengineer1@yahoo.com wrote:
> > Hi,
> >
> > I have one to many (from left to right)
> > relationships among the below tables
> > in my database
> >
> > user -> house -> contract -> contract status
> > |
> >
> > Also, a single house has a single provider and the
> > provider has multiple
> > rates inside the provider_rate table in
> > chronological order.
> >
> > I have a query to return the latest contract and
> > contract status for a
> > house... What i am trying to do is to get the rate
> > of electricity for the
> > latest contract... I am trying to retrieve the
> > latest provider rate before a
> > contract is signed.
> >
> > (Please see section marked with >> below).
> >
> > Here is the latest version of the SQL and it does
> > not work (see
> > ce_contract.contract_created)
> >
> > SELECT
> >
> ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_status
> > FROM ce_house
> > LEFT JOIN ce_provider_rate ON
> > ce_provider_rate.provider_id=ce_house.provider_id
> > LEFT JOIN ce_contract ON
> > ce_house.house_id=ce_contract.house_id
> > LEFT JOIN ce_contract_status ON
> >
> ce_contract.contract_id=ce_contract_status.contract_id
> > WHERE
> > ce_contract.contract_id IN (SELECT
> > MAX(ce_contract.contract_id) FROM
> > ce_contract GROUP BY ce_contract.house_id)
> > AND
> > ce_contract_status.contract_status_id IN (SELECT
> > MAX(ce_contract_status.contract_status_id) FROM
> > ce_contract_status GROUP BY
> > ce_contract_status.contract_id)
> > AND
> > >> ce_provider_rate.provider_rate_id IN (SELECT
> > MAX(ce_provider_rate.provider_rate_id) FROM
> > ce_provider_rate WHERE
> >
> ce_provider_rate.provider_rate_created<=ce_contract.contract_created)
> > AND
> > ce_house.house_id='1'
> >
> >
> > I would appreciate any insight to help me solve this
> > issue...
>
> Burak, having battled a couple big queries over that
> last week, my first piece of advice is simplify the
> query to the area that is giving you the trouble...
> iow, set up your query such that it *only* pulls the
> latest contract rate and leave all the noise out (you
> already know how to do the noise and can add it back
> in later).
>
> the query you want seems very similar to a subquery i
> recently put together.
>
> instead of the latest date contract rate by house, i
> needed the latest value of a boolean by inspection
> node.
>
> a simplified version of the query i used is here...
>
> http://www.rafb.net/paste/results/m322aH47.html
>
> of course, you have to adjust for different table
> relations.
>
> the query, as it stands, lists *all* latest result
> grouped by inspection_id.
>
> you'll be adding...
>
> AND ce_house.house_id='1' and add associated FROM
> tables and WHERE equations.
>
> you can specify
>
> AND ce_house.house_id='1'
>
> to limit the results further.
>
> this is my best try at the partial query given i can't
> totally understand your table structure...
>
> http://www.rafb.net/paste/results/MlJUrO80.html
>
> if i guessed wrong, adjust for your actual table
> structure.
>
> good luck.
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue
@ 2006-06-07 00:05 Michael Glaesemann <grzm@seespotcode.net>
parent: Kenneth B Hill <ken@scottshill.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Glaesemann @ 2006-06-07 00:05 UTC (permalink / raw)
To: ken@scottshill.com; +Cc: operationsengineer1@yahoo.com, Burak Seydioglu <buraks78@gmail.com>; pgsql-sql
On Jun 7, 2006, at 8:53 , Kenneth B Hill wrote:
> Make a "view" with a
> query, then perform a query using the view, etc. , then drop all views
> in the SQL script. This may make the entire operation perform faster.
I don't know how using a view would improve performance. However, it
may make the overall query more tractable by encapsulating portions
of it using views.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue
@ 2006-06-07 17:51 Burak Seydioglu <buraks78@gmail.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 10+ messages in thread
From: Burak Seydioglu @ 2006-06-07 17:51 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: ken@scottshill.com; operationsengineer1@yahoo.com; pgsql-sql
My current solution to the problem is PHP and it - simply put - sucks....
I loop through all the house records for a user using the following query
====================
SELECT * FROM ce_house
LEFT JOIN ce_contract ON ce_house.house_id=ce_contract.house_id
LEFT JOIN ce_contract_status ON
ce_contract.contract_id=ce_contract_status.contract_id
WHERE
ce_contract.contract_id IN (SELECT MAX(ce_contract.contract_id) FROM
ce_contract GROUP BY ce_contract.house_id)
AND
ce_contract_status.contract_status_id IN (SELECT
MAX(ce_contract_status.contract_status_id) FROM ce_contract_status GROUP BY
ce_contract_status.contract_id)
AND
ce_house.user_id='1'
====================
and then submit an additional query for each result to find the provider
rate by comparing ce_contract_created and ce_provider_rate created.
====================
SELECT provider_rate_amount FROM ce_provider_rate WHERE
provider_id='".$row["provider_id"]."' AND
provider_rate_created<='".$row["contract_created"]."' ORDER BY
provider_rate_created DESC LIMIT 1 OFFSET 0
====================
Table structures:
ce_house
====================
house_id
provider_id
ce_contract
====================
contract_id
house_id
contract_term
contract_created
ce_contract_status
====================
contract_status_id
contract_id
contract_status
contract_status_created
ce_provider
====================
provider_id
provider_name
ce_provider_rate
====================
provider_rate_id
provider_id
provider_rate_amount
provider_rate_created
Would I violate design principles if I create a new field called
"ce_contract_rate" under "ce_contract" and populate it as soon as a new
contract is created instead of looking it up from the ce_provide_rate table
everytime i need it?
Regards,
Burak
On 6/6/06, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
>
> On Jun 7, 2006, at 8:53 , Kenneth B Hill wrote:
>
> > Make a "view" with a
> > query, then perform a query using the view, etc. , then drop all views
> > in the SQL script. This may make the entire operation perform faster.
>
> I don't know how using a view would improve performance. However, it
> may make the overall query more tractable by encapsulating portions
> of it using views.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Join issue
@ 2006-06-07 19:04 operationsengineer1@yahoo.com
parent: Burak Seydioglu <buraks78@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-06-07 19:04 UTC (permalink / raw)
To: Burak Seydioglu <buraks78@gmail.com>; Michael Glaesemann <grzm@seespotcode.net>; +Cc: ken@scottshill.com; operationsengineer1@yahoo.com; pgsql-sql
> Table structures:
>
> ce_house
> ====================
> house_id
> provider_id
>
> ce_contract
> ====================
> contract_id
> house_id
> contract_term
> contract_created
>
> ce_contract_status
> ====================
> contract_status_id
> contract_id
> contract_status
> contract_status_created
>
> ce_provider
> ====================
> provider_id
> provider_name
>
> ce_provider_rate
> ====================
> provider_rate_id
> provider_id
> provider_rate_amount
> provider_rate_created
if i'm reading this right, a house can only have one
provider, but a house can have multiple contracts. at
first glance, i'd think the contracts should be
associated with the provider, not the house. if true,
you need to update your table structure.
is the rate associated with the contract (i'd assume
so with limited informationthink so) or the provider
(the way you have it set up)?
this is how i envision the table structure (granted, i
have limited information so i coul dbe way off
base)...
ce_house
====================
house_id
ce_provider
====================
provider_id
** house_id (fkey) **
provider_name
ce_contract
====================
contract_id
** provider_id (fkey) **
contract_term
contract_created
ce_contract_status
====================
contract_status_id
contract_id (fkey)
contract_status
contract_status_created
** ce_contract_rate **
====================
contract_rate_id
contract_id (fkey)
contract_rate_amount
contract_rate_created
i also assume, based on your structure, that you can
have multiple contract statuses for a given contract.
if not,you could probably delete that table and just
add contract_status and contract_status_created to
ce_contract.
i'm sorry if i'm way off base, but i'm trying to wrap
my head around the table strcuture, but i'm not
necessarily familiar with all the business rules that
created the structure - so i may be way off base here.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-06-07 19:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-15 15:10 Join issue? Marian POPESCU <softexpert@libertysurf.fr>
2004-12-15 15:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-12-15 15:21 Join issue? Marian POPESCU <softexpert@libertysurf.fr>
2006-06-06 18:10 Join issue Burak Seydioglu <buraks78@gmail.com>
2006-06-06 18:27 ` Re: Join issue Andrew Sullivan <ajs@crankycanuck.ca>
2006-06-06 22:33 ` Re: Join issue operationsengineer1@yahoo.com
2006-06-06 23:53 ` Re: Join issue Kenneth B Hill <ken@scottshill.com>
2006-06-07 00:05 ` Re: Join issue Michael Glaesemann <grzm@seespotcode.net>
2006-06-07 17:51 ` Re: Join issue Burak Seydioglu <buraks78@gmail.com>
2006-06-07 19:04 ` Re: Join issue operationsengineer1@yahoo.com
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