pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [sql]Joins
10+ messages / 6 participants
[nested] [flat]
* Re: [sql]Joins
@ 2000-11-09 16:24 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Najm Hashmi <najm@mondo-live.com>
0 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2000-11-09 16:24 UTC (permalink / raw)
To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql
On Fri, 10 Nov 2000, Najm Hashmi wrote:
> I am facing a dilemma at my work, I am using postgres first time. For
> some reason, my co-workers think that in Postgres joins i.e.
> simple joins of two or three tables are so bad that their cost is
> exponential. They believe that postgres simply takes Cartesian product
> of joining table in order to obtain desired tuples i.e. no optimization
> is done.
> I refused to believe so. I need your help to convince
> them that it is okay to join tables --two or three tables :)-- so we can
> eliminate redundancies from the database. I also want to know how
> postgres optimizes a join query. Thank you very much your help.
Umm, I don't know where they got that idea. Tom Lane can go into details
as the optimizer guru, but as a start, if you use EXPLAIN on your queries,
the system will tell you what plan it would use if you were to run the
query. That will get you some idea of what the system is doing.
I've had no problems really until about 8-12 tables joined when you might
tickle a bug in some versions of postgres which cause bogus plans to be
generated.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [sql]Joins
@ 2000-11-09 18:59 Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 10+ messages in thread
From: Cristóvão B. B. Dalla Costa @ 2000-11-09 18:59 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
> I've had no problems really until about 8-12 tables joined when you might
> tickle a bug in some versions of postgres which cause bogus plans to be
> generated.
I've had queries with over 25 joins and self-joins with no problems
whatsoever.
^ permalink raw reply [nested|flat] 10+ messages in thread
* [sql]Joins
@ 2000-11-10 15:59 Najm Hashmi <najm@mondo-live.com>
0 siblings, 1 reply; 10+ messages in thread
From: Najm Hashmi @ 2000-11-10 15:59 UTC (permalink / raw)
To: pgsql-sql
I am facing a dilemma at my work, I am using postgres first time. For
some reason, my co-workers think that in Postgres joins i.e.
simple joins of two or three tables are so bad that their cost is
exponential. They believe that postgres simply takes Cartesian product
of joining table in order to obtain desired tuples i.e. no optimization
is done.
I refused to believe so. I need your help to convince
them that it is okay to join tables --two or three tables :)-- so we can
eliminate redundancies from the database. I also want to know how
postgres optimizes a join query. Thank you very much your help.
Regards
Najm
^ permalink raw reply [nested|flat] 10+ messages in thread
* Joins!!
@ 2001-11-13 05:45 Gurudutt <guru@indvalley.com>
0 siblings, 1 reply; 10+ messages in thread
From: Gurudutt @ 2001-11-13 05:45 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have few set of questions for the PGSQL list users
Q 1. Consider the following query, suppose I want the sum of the
tickettabcount and ticketmultabcount, how do I modify my query to get the
same. If I use operator "+" in between these, it's been ignored by the
PgSQL and throws a error!!
QUERY
-----
select count(tickettab.tokenid) as tickettabcount,
(select count(tokenid) from ticketmultab
where agentid='danny' and arrivaldate='2001-11-12') as
ticketmultabcount
from tickettab
where tickettab.agentid='danny' and
tickettab.arrivaldate='2001-11-12'
RESULT
-------
tickettabcount || ticketmultabcount
------------- ------------------
9 2
Q 2. I join two tables, I get a result set which has values from
both the tables, now if I want to sort the combination of the result
set how do I do that!!, not the individual table result set.
Hoping for positive reply, thanks!!
--
Best regards,
Gurudutt mailto:guru@indvalley.com
Life is not fair - get used to it.
Bill Gates
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Joins!!
@ 2001-11-13 09:35 Haller Christoph <ch@rodos.fzk.de>
parent: Gurudutt <guru@indvalley.com>
0 siblings, 1 reply; 10+ messages in thread
From: Haller Christoph @ 2001-11-13 09:35 UTC (permalink / raw)
To: guru@indvalley.com; +Cc: pgsql-sql
>
> Q 1. Consider the following query, suppose I want the sum of the
> tickettabcount and ticketmultabcount, how do I modify my query to get the
> same. If I use operator "+" in between these, it's been ignored by the
> PgSQL and throws a error!!
>
> QUERY
> -----
> select count(tickettab.tokenid) as tickettabcount,
> (select count(tokenid) from ticketmultab
> where agentid='danny' and arrivaldate='2001-11-12') as
> ticketmultabcount
> from tickettab
> where tickettab.agentid='danny' and
> tickettab.arrivaldate='2001-11-12'
>
> RESULT
> -------
>
>
> tickettabcount || ticketmultabcount
> ------------- ------------------
> 9 2
>
Don't use the column name aliasing and it should work:
select sum(tickettab.tokenid) +
(select sum(tokenid) from ticketmultab
where agentid='danny' and arrivaldate='2001-11-12')
from tickettab
where tickettab.agentid='danny' and
tickettab.arrivaldate='2001-11-12' ;
>
>
> Q 2. I join two tables, I get a result set which has values from
> both the tables, now if I want to sort the combination of the result
> set how do I do that!!, not the individual table result set.
>
What is so mysterious about your join?
To answer your question, it would be very helpful to see the query
instead of reading prose.
Reagrds, Christoph
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Joins!!
@ 2001-11-17 04:10 Gurudutt <guru@indvalley.com>
parent: Haller Christoph <ch@rodos.fzk.de>
0 siblings, 0 replies; 10+ messages in thread
From: Gurudutt @ 2001-11-17 04:10 UTC (permalink / raw)
To: Haller Christoph <ch@rodos.fzk.de>; +Cc: pgsql-sql; pgsql-php@postgresql.org
Hello Haller,
Thanks for the reply, although I found a peculiar bug in PhpPgAdmin
(Ver: 2.2.1),the web client for postgres. It used to escape the "+" operator
everytime I used it, but then tried the same query in psql, it
worked!!
I have two tables
1. tickettab
2. ticketmultab
Both the tables have arrivaldate and arrivaltime as fields
I want to sort the combination of the result set, like
suppose an entry exists in tickettab as 2001-11-12 12:30
and the next entry in tickettab is 2001-11-12 16:40
if there exists a entry in ticketmultab as 2001-11-12 13.30
then I should get the results as follows
ticketid arrival date arrival time
1 2001-11-12 12:30:00 -- tickettab entry
2 2001-11-12 13:30:00 --ticketmultab entry
3 2001-11-12 16:40:00 -- tickettab entry
so depending on the arrival date and arrival time I need the result
set to be sorted.
I used bubble sort in php by moving the result set into an array and
then sorting it. Is there any other way to do this thru SQL.
--
Best regards,
Gurudutt mailto:guru@indvalley.com
Life is not fair - get used to it.
Bill Gates
Wednesday, November 14, 2001, 4:05:24 AM, you wrote:
>>
>> Q 1. Consider the following query, suppose I want the sum of the
>> tickettabcount and ticketmultabcount, how do I modify my query to get the
>> same. If I use operator "+" in between these, it's been ignored by the
>> PgSQL and throws a error!!
>>
>> QUERY
>> -----
>> select count(tickettab.tokenid) as tickettabcount,
>> (select count(tokenid) from ticketmultab
>> where agentid='danny' and arrivaldate='2001-11-12') as
>> ticketmultabcount
>> from tickettab
>> where tickettab.agentid='danny' and
>> tickettab.arrivaldate='2001-11-12'
>>
>> RESULT
>> -------
>>
>>
>> tickettabcount || ticketmultabcount
>> ------------- ------------------
>> 9 2
>>
HC> Don't use the column name aliasing and it should work:
HC> select sum(tickettab.tokenid) +
HC> (select sum(tokenid) from ticketmultab
HC> where agentid='danny' and arrivaldate='2001-11-12')
HC> from tickettab
HC> where tickettab.agentid='danny' and
HC> tickettab.arrivaldate='2001-11-12' ;
>>
>>
>> Q 2. I join two tables, I get a result set which has values from
>> both the tables, now if I want to sort the combination of the result
>> set how do I do that!!, not the individual table result set.
>>
HC> What is so mysterious about your join?
HC> To answer your question, it would be very helpful to see the query
HC> instead of reading prose.
HC> Reagrds, Christoph
HC> ---------------------------(end of broadcast)---------------------------
HC> TIP 3: if posting/reading through Usenet, please send an appropriate
HC> subscribe-nomail command to majordomo@postgresql.org so that your
HC> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 10+ messages in thread
* Joins~
@ 2001-11-21 08:37 Gurudutt <guru@indvalley.com>
0 siblings, 1 reply; 10+ messages in thread
From: Gurudutt @ 2001-11-21 08:37 UTC (permalink / raw)
To: pgsql-sql
This may be just a re-post of my previous mail, but unfortunately, I
haven't got any reply whatsoever to this problem, Is the question too
easy to answer or what, I couldn't get what it meant. I am posting the
problem again, hopefully somebody will be able to give me a solution.
----------------------------------------------------------------------
I have two tables
1. tickettab
2. ticketmultab
Both the tables have arrivaldate and arrivaltime as fields
I want to sort the combination of the result set, like
suppose an entry exists in tickettab as 2001-11-12 12:30
and the next entry in tickettab is 2001-11-12 16:40
if there exists a entry in ticketmultab as 2001-11-12 13.30
then I should get the results as follows
ticketid arrival date arrival time
1 2001-11-12 12:30:00 -- tickettab entry
2 2001-11-12 13:30:00 --ticketmultab entry
3 2001-11-12 16:40:00 -- tickettab entry
so depending on the arrival date and arrival time I need the result
set to be sorted.
I used bubble sort in php by moving the result set into an array and
then sorting it. Is there any other way to do this thru SQL.
--
Best regards,
Gurudutt mailto:guru@indvalley.com
Life is not fair - get used to it.
Bill Gates
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Joins~
@ 2001-11-21 11:57 Markus Bertheau <twanger@bluetwanger.de>
parent: Gurudutt <guru@indvalley.com>
0 siblings, 1 reply; 10+ messages in thread
From: Markus Bertheau @ 2001-11-21 11:57 UTC (permalink / raw)
To: Gurudutt <guru@indvalley.com>; +Cc: pgsql-sql
On Wed, 2001-11-21 at 09:37, Gurudutt wrote:
> I want to sort the combination of the result set, like
> suppose an entry exists in tickettab as 2001-11-12 12:30
> and the next entry in tickettab is 2001-11-12 16:40
>
> if there exists a entry in ticketmultab as 2001-11-12 13.30
> then I should get the results as follows
>
>
> ticketid arrival date arrival time
>
> 1 2001-11-12 12:30:00 -- tickettab entry
> 2 2001-11-12 13:30:00 --ticketmultab entry
> 3 2001-11-12 16:40:00 -- tickettab entry
maybe it works like this:
select * from (<join tickettab and ticketmultab however you want to) as
ticketjoined order by ticketjoined.arrivaldate, ticketjoined.arrivaltime
let me know if it works.
Markus Bertheau
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Joins~
@ 2001-11-22 10:40 Gurudutt <guru@indvalley.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 10+ messages in thread
From: Gurudutt @ 2001-11-22 10:40 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
Hello Markus,
Doesn't seem to be working, markus , I tried this query
-- QUERY ---
select tickettab.tokenid, ticketmultab.techcode,ticketmultab.techcode,
tickettab.problemstmt,ticketmultab.problemstmt
from (tickettab LEFT JOIN ticketmultab ON tickettab.tokenid=ticketmultab.tokenid)
as ticketjoinedtab order by
ticketjoinedtab.arrivaldate desc,
ticketjoinedtab.arrivaltime desc
--- END QUERY --
Error I got is
---- ERROR ----
ERROR: Column reference "arrivaldate" is ambiguous
------ END ERROR --
Are there any modifications that I need to do, to the query. Thanks
for the reply
--
Best regards,
Gurudutt mailto:guru@indvalley.com
Life is not fair - get used to it.
Bill Gates
Wednesday, November 21, 2001, 5:27:59 PM, you wrote:
MB> On Wed, 2001-11-21 at 09:37, Gurudutt wrote:
>> I want to sort the combination of the result set, like
>> suppose an entry exists in tickettab as 2001-11-12 12:30
>> and the next entry in tickettab is 2001-11-12 16:40
>>
>> if there exists a entry in ticketmultab as 2001-11-12 13.30
>> then I should get the results as follows
>>
>>
>> ticketid arrival date arrival time
>>
>> 1 2001-11-12 12:30:00 -- tickettab entry
>> 2 2001-11-12 13:30:00 --ticketmultab entry
>> 3 2001-11-12 16:40:00 -- tickettab entry
MB> maybe it works like this:
MB> select * from (<join tickettab and ticketmultab however you want to) as
MB> ticketjoined order by ticketjoined.arrivaldate, ticketjoined.arrivaltime
MB> let me know if it works.
MB> Markus Bertheau
MB> ---------------------------(end of broadcast)---------------------------
MB> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Joins~
@ 2001-11-23 07:55 Markus Bertheau <twanger@bluetwanger.de>
parent: Gurudutt <guru@indvalley.com>
0 siblings, 0 replies; 10+ messages in thread
From: Markus Bertheau @ 2001-11-23 07:55 UTC (permalink / raw)
To: Gurudutt <guru@indvalley.com>; +Cc: pgsql-sql
> select tickettab.tokenid, ticketmultab.techcode,ticketmultab.techcode,
> tickettab.problemstmt,ticketmultab.problemstmt
> from (tickettab LEFT JOIN ticketmultab ON tickettab.tokenid=ticketmultab.tokenid)
> as ticketjoinedtab order by
> ticketjoinedtab.arrivaldate desc,
> ticketjoinedtab.arrivaltime desc
Oh, I see, joining is of course false, my fault. must be like this, i
think:
select <fields> from tablea union select <the same fields> from tableb
order by <field>
I tried it with two sample tables on 7.1.3 and it worked:
bert=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
bert=> \d ta
Table "ta"
Attribute | Type | Modifier
-----------+---------+-----------------------------------------------
id | integer | not null default nextval('"ta_id_seq"'::text)
data | text |
i | integer |
Index: ta_id_key
bert=> \d tb
Table "tb"
Attribute | Type | Modifier
-----------+---------+------------------------------------
id | integer | default nextval('ta_id_seq'::text)
data | text |
i | integer |
bert=> select * from ta;
id | data | i
----+------+---
1 | qwe | 1
2 | ert | 3
3 | tzu | 5
(3 rows)
bert=> select * from tb;
id | data | i
----+------+---
4 | wer | 2
5 | rtz | 4
6 | zui | 6
(3 rows)
bert=> select * from ta union select * from tb order by i;
id | data | i
----+------+---
1 | qwe | 1
4 | wer | 2
2 | ert | 3
5 | rtz | 4
3 | tzu | 5
6 | zui | 6
(6 rows)
Markus Bertheau
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-11-23 07:55 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-10 15:59 [sql]Joins Najm Hashmi <najm@mondo-live.com>
2000-11-09 16:24 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-11-09 18:59 ` Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2001-11-13 05:45 Joins!! Gurudutt <guru@indvalley.com>
2001-11-13 09:35 ` Re: Joins!! Haller Christoph <ch@rodos.fzk.de>
2001-11-17 04:10 ` Re: [SQL] Joins!! Gurudutt <guru@indvalley.com>
2001-11-21 08:37 Joins~ Gurudutt <guru@indvalley.com>
2001-11-21 11:57 ` Re: Joins~ Markus Bertheau <twanger@bluetwanger.de>
2001-11-22 10:40 ` Re: Joins~ Gurudutt <guru@indvalley.com>
2001-11-23 07:55 ` Re: Joins~ Markus Bertheau <twanger@bluetwanger.de>
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