pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoin-Question
22+ messages / 16 participants
[nested] [flat]
* Join-Question
@ 2000-01-23 18:38 Dorthe Luebbert <dorthe@luebbert.net>
2000-01-23 22:38 ` Re: [SQL] Join-Question Borek.Lupomesky@ujep.cz
2000-01-24 20:09 ` Re: [SQL] Join-Question De Moudt Walter <wdemoudt@planetinternet.be>
0 siblings, 2 replies; 22+ messages in thread
From: Dorthe Luebbert @ 2000-01-23 18:38 UTC (permalink / raw)
To: pgsql-sql@hub.org; +Cc: luebbert@globalpark.de
Hi,
I have a problem retrieving information from serveral
different tables.
For example:
I have three tables:
Table 1 contains that person X has the hobby nr. 1, person 2 has
hobby nr 42 etc (fields: person_id, hobby_nr)
Table 2: Hobby nr 1 is "soccer", hobby nr 2 is "jazz" (fields:
hobby_nr, hobby_text) Table 3 contains the first and last name for
the persons in table nr 1 (fields person_id, first_name, last_name).
Now I want to find out for example the following:
If someone looks for a hobby, find out the first and last name of
those who like hobby nr 1. If someone does not look for hobby, just
print out all the names in the database.
In MySQL I would do something like a LEFT JOIN, wouldn´t I? But in
Postgres I could not find anything about left or right joins. Any
idea how to solve this problem??
Thanx so much for your help
Dorthe
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] Join-Question
2000-01-23 18:38 Join-Question Dorthe Luebbert <dorthe@luebbert.net>
@ 2000-01-23 22:38 ` Borek.Lupomesky@ujep.cz
1 sibling, 0 replies; 22+ messages in thread
From: Borek.Lupomesky@ujep.cz @ 2000-01-23 22:38 UTC (permalink / raw)
To: Dorthe Luebbert <dorthe@luebbert.net>; +Cc: pgsql-sql@hub.org; luebbert@globalpark.de
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Sun, 23 Jan 2000, Dorthe Luebbert wrote:
> Hi,
>
> I have a problem retrieving information from serveral
> different tables.
>
> For example:
>
> I have three tables:
>
> Table 1 contains that person X has the hobby nr. 1, person 2 has
> hobby nr 42 etc (fields: person_id, hobby_nr)
> Table 2: Hobby nr 1 is "soccer", hobby nr 2 is "jazz" (fields:
> hobby_nr, hobby_text) Table 3 contains the first and last name for
> the persons in table nr 1 (fields person_id, first_name, last_name).
>
> Now I want to find out for example the following:
>
> If someone looks for a hobby, find out the first and last name of
> those who like hobby nr 1. If someone does not look for hobby, just
> print out all the names in the database.
>
> In MySQL I would do something like a LEFT JOIN, wouldn´t I? But in
> Postgres I could not find anything about left or right joins. Any
> idea how to solve this problem??
Unfortunately, PostgreSQL doesn't support outer joins. The suggested
workaround is to do normal inner join and merge it (through UNION clause)
with a query where you select entries with given attribute having NULL
value.
Simple example:
SELECT a.t1, a.t2 FROM table1 t1, table2 t2 WHERE a.t1 = b.t2
UNION
SELECT a.t1, NULL FROM table1 t1, table2 t2 WHERE t2.a IS NULL;
(I hope I haven't mistyped something).
Bye Borek
- --
=====================================================================
BOREK LUPOMESKY, network administrator University of J. E. Purkyne
Ceske mladeze 8
WWW: http://www.ujep.cz/~lupomesk/ Usti nad Labem, 40096
IRCnet: Borek @ #usti The Czech Republic
PGP keyid: B6A06AEB tel: +420-602-376368
==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE4i4LfA6dWI7agausRApygAKCqYkW+mK5y3L/8tTRXQI5JZMEFBACgp28x
YMXelzl02S5F2D5VDlrTOgc=
=qKB8
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] Join-Question
2000-01-23 18:38 Join-Question Dorthe Luebbert <dorthe@luebbert.net>
@ 2000-01-24 20:09 ` De Moudt Walter <wdemoudt@planetinternet.be>
1 sibling, 0 replies; 22+ messages in thread
From: De Moudt Walter @ 2000-01-24 20:09 UTC (permalink / raw)
To: dorthe@luebbert.net; +Cc: pgsql-sql@hub.org; luebbert@globalpark.de
Dorthe Luebbert wrote:
>
> Hi,
>
> I have a problem retrieving information from serveral
> different tables.
>
> For example:
>
> I have three tables:
>
> Table 1 contains that person X has the hobby nr. 1, person 2 has
> hobby nr 42 etc (fields: person_id, hobby_nr)
> Table 2: Hobby nr 1 is "soccer", hobby nr 2 is "jazz" (fields:
> hobby_nr, hobby_text) Table 3 contains the first and last name for
> the persons in table nr 1 (fields person_id, first_name, last_name).
>
> Now I want to find out for example the following:
>
> If someone looks for a hobby, find out the first and last name of
> those who like hobby nr 1. If someone does not look for hobby, just
> print out all the names in the database.
>
> In MySQL I would do something like a LEFT JOIN, wouldn´t I? But in
> Postgres I could not find anything about left or right joins. Any
> idea how to solve this problem??
>
> Thanx so much for your help
>
> Dorthe
>
> ************
Hi,
Here's how i did it : (sorry; long message)
These are the tables : (all fields are varchar() )
select * from hobbydata;
hobby_id|hobbyname
--------+---------------
3|trumpet playing
2|coocking
1|relativating
4|carrot hunting
(4 rows)
select * from persondata;
person_id|firstname|lastname
---------+---------+--------
1|Jan |Claessen
2|Hannibal |Lecter
3|Albert |Einstein
4|Jack |Rabbit
(4 rows)
select * from persons;
person_id|hobby_id
---------+--------
1| 1
1| 3
3| 1
2| 2
4| 4
(5 rows)
select * from hobbydata, persondata, persons where
persons.hobby_id = hobbydata.hobby_id and
persons.person_id = persondata.person_id;
hobby_id|hobbyname |person_id|firstname|lastname|person_id|hobby_id
--------+---------------+---------+---------+--------+---------+--------
3|trumpet playing| 1|Jan |Claessen| 1| 3
1|relativating | 1|Jan |Claessen| 1| 1
2|coocking | 2|Hannibal |Lecter | 2| 2
1|relativating | 3|Albert |Einstein| 3| 1
4|carrot hunting | 4|Jack |Rabbit | 4| 4
(5 rows)
# now the view :
create view whodoeswhat as select hobbydata.hobby_id, hobbyname,
persondata.person_id, firstname, lastname from hobbydata, persondata,
persons where persons.hobby_id = hobbydata.hobby_id and
persons.person_id = persondata.person_id;
select * from whodoeswhat;
hobby_id|hobbyname |person_id|firstname|lastname
--------+---------------+---------+---------+--------
3|trumpet playing| 1|Jan |Claessen
1|relativating | 1|Jan |Claessen
2|coocking | 2|Hannibal |Lecter
1|relativating | 3|Albert |Einstein
4|carrot hunting | 4|Jack |Rabbit
(5 rows)
select firstname, lastname from whodoeswhat
where hobbyname = 'relativating';
firstname|lastname
---------+--------
Jan |Claessen
Albert |Einstein
(2 rows)
Seems to work :-)
May seem extensive work, but it's not THAT much, and afterwards you can
rely on one view that contains all information.
^ permalink raw reply [nested|flat] 22+ messages in thread
* JOIN question
@ 2001-12-22 19:26 Frank Morton <fmorton@base2inc.com>
2001-12-22 22:57 ` Re: JOIN question Ian Barwick <barwick@gmx.net>
2001-12-22 23:27 ` Re: JOIN question Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 22+ messages in thread
From: Frank Morton @ 2001-12-22 19:26 UTC (permalink / raw)
To: pgsql-sql
I'm looking for the most portable way to do the following,
given these two tables:
Table 1 is called "content" which contains an integer "id" column.
The value of this id is "1" for this example.
Table 2 is called "protection" and keeps track of who can read
and write the content object, so this table may contain multiple
protection settings for a single content object.
Simplifying, to consider my problem, the protection table contains
a column called "contentId", connecting it to the content table id.
This table has three rows in it for three groups that can read it
with contentId set to "1".
If I do the query:
select Content.* from Content,Protection
where (Content.id = Protection.contentId);
I get three rows back, corresponding to each group that has
access to the content. However, I would like to get back
just one row, corresponding to the content that fits the desired
protections.
How do I do this?
Thanks for answering this question for the relative sql newbie.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN question
2001-12-22 19:26 JOIN question Frank Morton <fmorton@base2inc.com>
@ 2001-12-22 22:57 ` Ian Barwick <barwick@gmx.net>
1 sibling, 0 replies; 22+ messages in thread
From: Ian Barwick @ 2001-12-22 22:57 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; pgsql-sql
On Saturday 22 December 2001 20:26, Frank Morton wrote:
> I'm looking for the most portable way to do the following,
> given these two tables:
>
> Table 1 is called "content" which contains an integer "id" column.
> The value of this id is "1" for this example.
>
> Table 2 is called "protection" and keeps track of who can read
> and write the content object, so this table may contain multiple
> protection settings for a single content object.
>
> Simplifying, to consider my problem, the protection table contains
> a column called "contentId", connecting it to the content table id.
> This table has three rows in it for three groups that can read it
> with contentId set to "1".
>
> If I do the query:
>
> select Content.* from Content,Protection
> where (Content.id = Protection.contentId);
>
> I get three rows back, corresponding to each group that has
> access to the content. However, I would like to get back
> just one row, corresponding to the content that fits the desired
> protections.
IIUC (if I understand correctly) you want to get each row of table
"Content" which is referenced at least once from "Protection"?
If so SELECT DISTINCT will be your friend:
SELECT DISTINCT Content.*
FROM Content,Protection
WHERE (Content.id = Protection.contentId)
or in ANSI rather than theta join style:
SELECT DISTINCT Content.*
FROM Content
INNER JOIN Protection ON (Content.id=Protection.contentId)
You could achieve the same result with a subselect, although
it may be slower:
SELECT *
FROM Content
WHERE Content.id IN (SELECT contentID FROM Protection)
HTH
Ian Barwick
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN question
2001-12-22 19:26 JOIN question Frank Morton <fmorton@base2inc.com>
@ 2001-12-22 23:27 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 22+ messages in thread
From: Peter Eisentraut @ 2001-12-22 23:27 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql
Frank Morton writes:
> If I do the query:
>
> select Content.* from Content,Protection
> where (Content.id = Protection.contentId);
>
> I get three rows back, corresponding to each group that has
> access to the content. However, I would like to get back
> just one row, corresponding to the content that fits the desired
> protections.
You didn't specify the "desired protection" anywhere within your query.
Possibly you want something like
SELECT * FROM content, protection WHERE content.id = protection.contentid
AND protection.name = 'something';
This will give you the content that "something" has access to, which may
be zero, one, or many rows.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 22+ messages in thread
* Join Question
@ 2002-09-17 14:17 Robert J. Sanford, Jr. <rsanford@trefs.com>
0 siblings, 0 replies; 22+ messages in thread
From: Robert J. Sanford, Jr. @ 2002-09-17 14:17 UTC (permalink / raw)
To: pgsql-sql
My office is working on a fantasy football database and, unfortunately, I
have been tagged as the DBA. I'm a bit weak on set theory but I'm trying.
Right now I am trying to calculate up game scores into the database rather
than running through code to do that. A baseline of my schema is that:
+ Each team has a schedule number associated with it. The schedule numbers
are linked to the game schedule table.
+ There is a game schedule table based on schedule numbers. This schedule is
fixed and doesn't change season to season. If you want to change who plays
who you change the team schedule numbers.
+ GameScheduleDetails is a view joining the team info with the schedule so I
can easily see who plays who.
+ There is a game roster table that lists which players each team played in
a given week.
+ After running stats through there is a table that lists the points for
each player per week.
+ GamePointDetails is a view joining the game rosters with the
weeklyplayerpoints.
+ The games table is a historical table that shows the scores between two
teams for a given week and season. The SQL to run the calcs is below.
I know that what is happening is that the join between the two teams scores
is generating 64 rows of player points for each team instead of eight (a
game roster consists of eight players). That means that the join that is
occuring is a natural join rather than the desired inner join which leads to
a multiplicative row count of 8 players from team 1 x 8 players from team 2
which is 64 rows per team. Since each player score is being counted 8 times
I am just dividing the sum of each team score by 8 and it is spitting out
the correct numbers. But, it is not working "properly". It is getting the
job done but I want to understand is how to make it work properly where I
don't have to do the divide by 8;
So my question is... can I get rid of the divide by 8 or am I stuck with it?
rjsjr
insert into games
(
season, weeknum,
teamid1, team1pointsscored, team1maxpointsfor,
teamid2, team2pointsscored, team2maxpointsfor
)
select
SEASON_NUM as Season,
WEEK_NUM as WeekNum,
gsd.team1id as Team1ID,
sum(gpd1.points) / 8 as Team1Score,
0 as Team1Max,
gsd.team2id as Team2ID,
sum(gpd2.points) / 8 as Team2Score,
0 as Team2Max
from
gamescheduledetails gsd
inner join gamepointdetails as gpd1 on
gpd1.teamid = gsd.team1id and
gpd1.weeknum = gsd.weeknum
inner join gamepointdetails as gpd2 on
gpd2.teamid = gsd.team2id and
gpd2.weeknum = gsd.weeknum
where
gsd.weeknum = WEEK_NUM and
gpd1.leagueid = gpd2.leagueid and
gpd1.season = gpd2.season and
gpd1.season = SEASON_NUM
group by
gsd.team1id, gsd.team1name, gsd.team2id, gsd.team2name
;
^ permalink raw reply [nested|flat] 22+ messages in thread
* join question
@ 2002-10-18 18:58 Frank Morton <fmorton@base2inc.com>
2002-10-18 19:31 ` Re: join question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-18 19:50 ` Re: join question Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 22+ messages in thread
From: Frank Morton @ 2002-10-18 18:58 UTC (permalink / raw)
To: pgsql-sql
For the SQL gurus, a query where I'm not getting the expected
results. Trying to write it using sql compatible with both postgres
and mysql.
There are two tables:
table = profile
int id
char name
table = attribute
int id
int containerId
char name
char value
Multiple attribute rows correspond to a single profile row where
attribute.containerId = profile.id
These two queries result in one row being returned, which is
the expected result:
select name from profile where ((profile.state='1020811'));
select profile.name from profile,attribute where (((attribute.name='description') and (attribute.value='1020704') and (profile.id=attribute.containerId)));
But, I thought this next query would just be a simple way to combine the two
queries with an "or" operator, still returning one row, actually returns
ALL rows of attribute:
select profile.name from profile,attribute where ((profile.state='1020811') or ((attribute.name='marketsegment') and (attribute.value='1020704') and (profile.id=attribute.containerId)));
Why doesn't this last query return just one row?
TIA
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: join question
2002-10-18 18:58 join question Frank Morton <fmorton@base2inc.com>
@ 2002-10-18 19:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 22+ messages in thread
From: Stephan Szabo @ 2002-10-18 19:31 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql
On Fri, 18 Oct 2002, Frank Morton wrote:
> For the SQL gurus, a query where I'm not getting the expected
> results. Trying to write it using sql compatible with both postgres
> and mysql.
>
> There are two tables:
>
> table = profile
> int id
> char name
>
> table = attribute
> int id
> int containerId
> char name
> char value
>
> Multiple attribute rows correspond to a single profile row where
> attribute.containerId = profile.id
>
> These two queries result in one row being returned, which is
> the expected result:
>
> select name from profile where ((profile.state='1020811'));
>
> select profile.name from profile,attribute where
> (((attribute.name='description') and (attribute.value='1020704') and
> (profile.id=attribute.containerId)));
>
> But, I thought this next query would just be a simple way to combine the two
> queries with an "or" operator, still returning one row, actually returns
> ALL rows of attribute:
>
> select profile.name from profile,attribute where
> ((profile.state='1020811') or ((attribute.name='marketsegment') and
> (attribute.value='1020704') and (profile.id=attribute.containerId)));
>
> Why doesn't this last query return just one row?
Because for each combination of rows from profile and attribute where
profile.state='1020811' the where clause is statisfied.
I'm not sure what you're precisely trying to get out, since unless the
row that matches each of the clauses is the same I don't see how you'd
only get one row out with an or.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: join question
2002-10-18 18:58 join question Frank Morton <fmorton@base2inc.com>
@ 2002-10-18 19:50 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 22+ messages in thread
From: Jean-Luc Lachance @ 2002-10-18 19:50 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql
I think you meant:
select profile.name
from profile,attribute
where ( profile.id = attribute.containerId)
and ( profile.state =' 1020811' or ( attribute.name = 'marketsegment'
and attribute.value = '1020704');
> select profile.name from profile,attribute where
> ((profile.state='1020811') or ((attribute.name='marketsegment') and
> (attribute.value='1020704') and (profile.id=attribute.containerId)));
>
> Why doesn't this last query return just one row?
>
> TIA
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* join question
@ 2002-11-22 19:44 Nathan Young <nyoung@asis.com>
2002-11-26 16:11 ` Re: join question Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 22+ messages in thread
From: Nathan Young @ 2002-11-22 19:44 UTC (permalink / raw)
To: pgsql-sql
Hi all.
I have a table with members and a table with payments. Each payment is
related to a member by memberID and each payment has (among other things) a
year paid.
I can create a join to find a list of members who have paid for a given year
(2002 in this case):
select member.memberID,member.name from member, payment where
payment.memberID = member.memberID and payment.yearPaid = 2002
I would like to be able to get a list of members who have not paid for a
given year.
I would also like to combine the two criteria, for example to generate a list
of members who have paid for 2002 but not 2003.
Thanks in advance!
--->Nathan
---
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
---
Nathan Young
N. C. Young Design
(530)629-4176
http://ncyoung.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: join question
2002-11-22 19:44 join question Nathan Young <nyoung@asis.com>
@ 2002-11-26 16:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-27 17:37 ` Re: join question Nathan Young <nyoung@asis.com>
0 siblings, 1 reply; 22+ messages in thread
From: Stephan Szabo @ 2002-11-26 16:11 UTC (permalink / raw)
To: nathan@ncyoung.com; +Cc: pgsql-sql
On Fri, 22 Nov 2002, Nathan Young wrote:
> Hi all.
>
> I have a table with members and a table with payments. Each payment is
> related to a member by memberID and each payment has (among other things) a
> year paid.
>
> I can create a join to find a list of members who have paid for a given year
> (2002 in this case):
>
> select member.memberID,member.name from member, payment where
> payment.memberID = member.memberID and payment.yearPaid = 2002
>
> I would like to be able to get a list of members who have not paid for a
> given year.
Well, I believe either of these two will do that:
select member.memberId, member.name from member where not exists (select
* from payment where payment.memberId=member.memberID and
payment.yearPaid=2002);
select member.memberId, member.name from member left outer join
(select * from payment where yearPaid=2002) as a using (memberId) where
yearPaid is null;
> I would also like to combine the two criteria, for example to generate a list
> of members who have paid for 2002 but not 2003.
I think these would do that:
select member.memberID,member.name from member, payment where
payment.memberID = member.memberID and payment.yearPaid = 1999
and not exists (select * from payment where
payment.memberId=member.memberId and yearPaid=2002);
select member.memberId, member.name from member inner join (select
* from payment where yearPaid=2002) as a using (memberId) left outer join
(select * from payment where yearPaid=2003) as b using (memberId) where
b.yearPaid is null;
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: join question
2002-11-22 19:44 join question Nathan Young <nyoung@asis.com>
2002-11-26 16:11 ` Re: join question Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-11-27 17:37 ` Nathan Young <nyoung@asis.com>
2002-11-28 08:10 ` Re: join question Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 22+ messages in thread
From: Nathan Young @ 2002-11-27 17:37 UTC (permalink / raw)
To: pgsql-sql
OK, that works great, but I was told that I should avoid sub-selects when
possible for performance reasons. Also, I used so much mental energy trying
to find a solution that would do either task using a join that I would be
very curious if anyone had a solution.
The setup:
>> I have a table with members and a table with payments. Each payment is
>> related to a member by memberID and each payment has (among other things)
>> a year paid.
The problem:
>> I would like to be able to get a list of members who have not paid for a
>> given year.
Two possible solutions, both using sub-selects:
> select member.memberId, member.name from member where not exists (select
> * from payment where payment.memberId=member.memberID and
> payment.yearPaid=2002);
>
> select member.memberId, member.name from member left outer join
> (select * from payment where yearPaid=2002) as a using (memberId) where
> yearPaid is null;
In addition to my interest in finding a join that could do that, I'm curios
about a couple other things.
My understanding is that exists is optimized so that the first version would
be faster than the second.
"using (memberID)" would be the same as "on member.memberID =
payment.memberID", right?
Thanks!
------------->Nathan
11/26/2002 8:11:53 AM, Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
>On Fri, 22 Nov 2002, Nathan Young wrote:
>
>> Hi all.
>>
>> I have a table with members and a table with payments. Each payment is
>> related to a member by memberID and each payment has (among other things)
a
>> year paid.
>>
>> I can create a join to find a list of members who have paid for a given
year
>> (2002 in this case):
>>
>> select member.memberID,member.name from member, payment where
>> payment.memberID = member.memberID and payment.yearPaid = 2002
>>
>> I would like to be able to get a list of members who have not paid for a
>> given year.
>
>Well, I believe either of these two will do that:
>
> select member.memberId, member.name from member where not exists (select
> * from payment where payment.memberId=member.memberID and
> payment.yearPaid=2002);
>
> select member.memberId, member.name from member left outer join
> (select * from payment where yearPaid=2002) as a using (memberId) where
> yearPaid is null;
>
>> I would also like to combine the two criteria, for example to generate a
list
>> of members who have paid for 2002 but not 2003.
>
>I think these would do that:
>
>select member.memberID,member.name from member, payment where
> payment.memberID = member.memberID and payment.yearPaid = 1999
> and not exists (select * from payment where
> payment.memberId=member.memberId and yearPaid=2002);
>
>select member.memberId, member.name from member inner join (select
> * from payment where yearPaid=2002) as a using (memberId) left outer join
> (select * from payment where yearPaid=2003) as b using (memberId) where
> b.yearPaid is null;
>
>
>
>
---
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
---
Nathan Young
N. C. Young Design
(530)629-4176
http://ncyoung.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: join question
2002-11-22 19:44 join question Nathan Young <nyoung@asis.com>
2002-11-26 16:11 ` Re: join question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-27 17:37 ` Re: join question Nathan Young <nyoung@asis.com>
@ 2002-11-28 08:10 ` Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 22+ messages in thread
From: Manfred Koizar @ 2002-11-28 08:10 UTC (permalink / raw)
To: nathan@ncyoung.com; +Cc: pgsql-sql
On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <nyoung@asis.com>
wrote:
>OK, that works great, but I was told that I should avoid sub-selects when
>possible for performance reasons.
>>
>> select member.memberId, member.name from member left outer join
>> (select * from payment where yearPaid=2002) as a using (memberId) where
>> yearPaid is null;
Nathan,
if you want a version without a subselect, try
SELECT m.memberId, m.name
FROM member AS m LEFT OUTER JOIN
payment AS p ON p.yearPaid=2002 AND m.memberId=p.memberId
WHERE p.memberId IS NULL;
though I don't know whether it is faster.
Servus
Manfred
^ permalink raw reply [nested|flat] 22+ messages in thread
* Join question
@ 2007-07-27 01:33 Paul Lambert <paul.lambert@autoledgers.com.au>
2007-07-27 01:46 ` Re: Join question Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 1 reply; 22+ messages in thread
From: Paul Lambert @ 2007-07-27 01:33 UTC (permalink / raw)
To: pgsql-sql
I have a database in a parts sales environment that I am having a little
difficulty with a join query - trying to figure out which way to join
things.
I have a parts_invoice_header table, containing the header record for
each invoice.
I have a parts_invoice_lines table, containing the parts details for
each invoice.
I have a parts_invoice_sundries table, containing additional sundry
charges (freight, delivery etc) on each invoice.
For each record in the invoice_header table, there can be 0 or more
records in either of the two other tables.
I want to search for an invoice in the header file and get the details
of all matching records from the lines and sundries. Assuming the lines
and sundries tables both have the same column names, I should be able to
end up with something like:
table | header header line/sundries
|
column| Invoice number customer item amount
line | 1111 abc 457ABC 10.00
sundry| 1111 abc FREIGHT 5.00
line | 1111 abc FGOIL 15.00
What would be the best method of joining to create a result-set such as
this?
TIA for any assistance,
P.
--
Paul Lambert
Database Administrator
AutoLedgers
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Join question
2007-07-27 01:33 Join question Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-07-27 01:46 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 0 replies; 22+ messages in thread
From: Phillip Smith @ 2007-07-27 01:46 UTC (permalink / raw)
To: pgsql-sql
This might give you a starting point if I understand you correctly...
SELECT h.invoice_number,
h.customer,
l.item,
l.amount
FROM lines AS l
JOIN headers AS h
UNION
SELECT h.invoice_number,
h.customer,
s.item,
s.amount
FROM sundries AS s
JOIN headers AS h
ORDER BY invoice_number, item
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Join question
@ 2007-07-27 01:48 Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-07-27 02:18 ` Re: Join question Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 1 reply; 22+ messages in thread
From: Phillip Smith @ 2007-07-27 01:48 UTC (permalink / raw)
To: pgsql-sql
Whoops, I forgot the JOIN conditions! Fixed below
-----Original Message-----
From: Phillip Smith [mailto:phillip.smith@weatherbeeta.com.au]
Sent: Friday, 27 July 2007 11:47
To: 'pgsql-sql@postgresql.org'
Subject: RE: [SQL] Join question
This might give you a starting point if I understand you correctly...
SELECT h.invoice_number,
h.customer,
l.item,
l.amount
FROM lines AS l
JOIN headers AS h ON l.invoice_number = h.invoice_number
UNION
SELECT h.invoice_number,
h.customer,
s.item,
s.amount
FROM sundries AS s
JOIN headers AS h ON s.invoice_number = h.invoice_number
ORDER BY invoice_number, item
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Join question
2007-07-27 01:48 Re: Join question Phillip Smith <phillip.smith@weatherbeeta.com.au>
@ 2007-07-27 02:18 ` Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 0 replies; 22+ messages in thread
From: Paul Lambert @ 2007-07-27 02:18 UTC (permalink / raw)
To: pgsql-sql
Phillip Smith wrote:
> Whoops, I forgot the JOIN conditions! Fixed below
>
> -----Original Message-----
> From: Phillip Smith [mailto:phillip.smith@weatherbeeta.com.au]
> Sent: Friday, 27 July 2007 11:47
> To: 'pgsql-sql@postgresql.org'
> Subject: RE: [SQL] Join question
>
>
> This might give you a starting point if I understand you correctly...
>
> SELECT h.invoice_number,
> h.customer,
> l.item,
> l.amount
> FROM lines AS l
> JOIN headers AS h ON l.invoice_number = h.invoice_number
> UNION
> SELECT h.invoice_number,
> h.customer,
> s.item,
> s.amount
> FROM sundries AS s
> JOIN headers AS h ON s.invoice_number = h.invoice_number
> ORDER BY invoice_number, item
>
Forgot all about union - I had two individual views, one for
invoice+parts, one for invoice+sundries... didn't think of union to
combine the two views together.
Perfect solution, you've done it for me again Phillip, thanks.
--
Paul Lambert
Database Administrator
AutoLedgers
^ permalink raw reply [nested|flat] 22+ messages in thread
* Join question
@ 2007-08-21 17:48 tyrrill_ed@emc.com
2007-08-21 19:16 ` Re: Join question Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 21:16 ` Re: Join question Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 2 replies; 22+ messages in thread
From: tyrrill_ed@emc.com @ 2007-08-21 17:48 UTC (permalink / raw)
To: pgsql-sql
Hey All,
I have a query I'm trying to speed up, and I was hoping someone could
help me. I have a three tables a and b hold data, and c just references
between a and b:
create table a (
a_id int,
x int
);
create table b (
b_id int,
x int
);
create table c (
a_id int,
b_id int
);
I am doing a query like this:
SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id =
c.b_id GROUP by a.x;
I only need to get one row from b for each row in a, and it really
doesn't matter which one. I use max() to get a single value from table
b. There are generally be dozens to hundreds of rows in b for each row
in a. The problem is when I have a query with tens of thousands of rows
in a that the join with b will have millions of rows, and is really
slow. The group by effectively reduces the results down to what I want,
but it still has to process the millions of rows. Does anyone know a
way I could restructure this query to get only one b for each a in a
faster way?
Thanks,
Ed Tyrrill
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Join question
2007-08-21 17:48 Join question tyrrill_ed@emc.com
@ 2007-08-21 19:16 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 22+ messages in thread
From: Richard Broersma Jr @ 2007-08-21 19:16 UTC (permalink / raw)
To: tyrrill_ed@emc.com; pgsql-sql
--- tyrrill_ed@emc.com wrote:
> create table c (
> a_id int,
> b_id int
> );
>
> I am doing a query like this:
>
> SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id =
> c.b_id GROUP by a.x;
>
> I only need to get one row from b for each row in a, and it really
> doesn't matter which one. I use max() to get a single value from table
> b. There are generally be dozens to hundreds of rows in b for each row
> in a. The problem is when I have a query with tens of thousands of rows
> in a that the join with b will have millions of rows, and is really
> slow. The group by effectively reduces the results down to what I want,
> but it still has to process the millions of rows. Does anyone know a
> way I could restructure this query to get only one b for each a in a
> faster way?
CREATE INDEX table_c_foreign_key ON c ( a, b );
SELECT a.x, b.x
FROM ( SELECT DISTINCT( a_id ) a_id, b_id
FROM c ) AS c( a_id, b_id )
INNER JOIN a
ON c.a_id = a.id
INNER JOIN b
ON c.b_id = b.id;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Join question
2007-08-21 17:48 Join question tyrrill_ed@emc.com
@ 2007-08-21 21:16 ` Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 22+ messages in thread
From: Michael Glaesemann @ 2007-08-21 21:16 UTC (permalink / raw)
To: tyrrill_ed@emc.com; +Cc: pgsql-sql
On Aug 21, 2007, at 12:48 , tyrrill_ed@emc.com wrote:
> SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id =
> c.b_id GROUP by a.x;
Shouldn't affect performance, but another way to write this which you
may find more readable is to list your join conditions with the joins
rather than grouping them all in the WHERE clause:
SELECT a.x, max(b.x)
FROM a
JOIN c USING (a_id)
JOIN b USING (b_id)
GROUP BY a.x;
You can also write this using NATURAL JOIN which joins on like-named
columns.
SELECT a.x, max(b.x)
FROM a
NATURAL JOIN c
JOIN b USING (b_id)
GROUP BY a.x;
You can't use NATURAL JOIN to join b because you've got columns named
x in both a and b that you're *not* joining on.
Another nice thing about the USING and NATURAL JOIN syntax is that
the result includes only one column for the joined columns, rather
than two. In this case, there would be only one b_id and one a_id
column in the result set. Using ON or putting the join condition in
the WHERE clause puts two b_id and two a_id columns in the result set.
> Does anyone know a
> way I could restructure this query to get only one b for each a in a
> faster way?
You might want to ask on the pgsql-performance list as well, as
people there are generally interested in improving query performance.
One thing they'll ask you for is the output of EXPLAIN ANALYZE for
your query.
Here's an alternative, but I don't know how it'd compare in terms of
performance:
EXPLAIN ANALYZE
SELECT DISTINCT ON (a.x)
a.x, b.x
FROM a
NATURAL JOIN c
JOIN b USING (b_id);
Give that a shot. (DISTINCT ON is a non-standard PostgreSQL
extension, if non-portable syntax is something you're looking to avoid.)
EXPLAIN ANALYZE shows you how the planner decided to proceed with the
query, which can be useful when comparing alternatives, so you can
compare using DISTINCT ON with your own query using max.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Join question
@ 2007-08-21 19:18 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 22+ messages in thread
From: Richard Broersma Jr @ 2007-08-21 19:18 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; tyrrill_ed@emc.com; pgsql-sql
oops...
I meant "DISTINCT ON ( a_id )"
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> SELECT a.x, b.x
> FROM ( SELECT DISTINCT ON ( a_id ) a_id, b_id
^^
> FROM c ) AS c( a_id, b_id )
> INNER JOIN a
> ON c.a_id = a.id
> INNER JOIN b
> ON c.b_id = b.id;
>
> Regards,
> Richard Broersma Jr.
>
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2007-08-21 21:16 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-23 18:38 Join-Question Dorthe Luebbert <dorthe@luebbert.net>
2000-01-23 22:38 ` Borek.Lupomesky@ujep.cz
2000-01-24 20:09 ` De Moudt Walter <wdemoudt@planetinternet.be>
2001-12-22 19:26 JOIN question Frank Morton <fmorton@base2inc.com>
2001-12-22 22:57 ` Re: JOIN question Ian Barwick <barwick@gmx.net>
2001-12-22 23:27 ` Re: JOIN question Peter Eisentraut <peter_e@gmx.net>
2002-09-17 14:17 Join Question Robert J. Sanford, Jr. <rsanford@trefs.com>
2002-10-18 18:58 join question Frank Morton <fmorton@base2inc.com>
2002-10-18 19:31 ` Re: join question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-18 19:50 ` Re: join question Jean-Luc Lachance <jllachan@nsd.ca>
2002-11-22 19:44 join question Nathan Young <nyoung@asis.com>
2002-11-26 16:11 ` Re: join question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-27 17:37 ` Re: join question Nathan Young <nyoung@asis.com>
2002-11-28 08:10 ` Re: join question Manfred Koizar <mkoi-pg@aon.at>
2007-07-27 01:33 Join question Paul Lambert <paul.lambert@autoledgers.com.au>
2007-07-27 01:46 ` Re: Join question Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-07-27 01:48 Re: Join question Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-07-27 02:18 ` Re: Join question Paul Lambert <paul.lambert@autoledgers.com.au>
2007-08-21 17:48 Join question tyrrill_ed@emc.com
2007-08-21 19:16 ` Re: Join question Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 21:16 ` Re: Join question Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 19:18 Re: Join question Richard Broersma Jr <rabroersma@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