Обсуждение: Slightly OT: outer joins
This is a little off topic but this is the best source of SQL knowledge I know about so hopefully this will be interesting enough for someone to answer. :-) I've got the following tables: Table people id fname lname 1 bob smith 2 tom jones 3 jane doe 4 mike porter Table food id favorite_food 2 eggrolls 3 ice cream Table color id color 1 red 3 blue I want a query to produce the result set: fname lname favorite_color favorite_food bob smith red null tom jones null eggrolls jane doe blue ice cream mike porter null null I'm having lots of trouble getting the right result or knowing whether this is even a valid usage of outer joins. Can somebody show me a working query? Thanks! -Fran
On Tue, 20 Nov 2001, Fran Fabrizio wrote:
> This is a little off topic but this is the best source of SQL knowledge
> I know about so hopefully this will be interesting enough for someone to
> answer. :-)
> I've got the following tables:
> Table people
> id fname lname
> 1 bob smith
> 2 tom jones
> 3 jane doe
> 4 mike porter
> Table food
> id favorite_food
> 2 eggrolls
> 3 ice cream
> Table color
> id color
> 1 red
> 3 blue
> I want a query to produce the result set:
> fname lname favorite_color favorite_food
> bob smith red null
> tom jones null eggrolls
> jane doe blue ice cream
> mike porter null null
> I'm having lots of trouble getting the right result or knowing whether
> this is even a valid usage of outer joins. Can somebody show me a
> working query? Thanks!
Hi Fran!
I'm a beginner, and maybe I will misinform you, but I think in the above
case you want your tables being joined by the ID column. In that case you
_should_ have a row in your auxiliary tables (color, food) for every
occuring IDs in the main table. It will solve your problem:
---
drop table people;
drop table food;
drop table color;
create table people(id int4,fname char(10),lname char(10));
create table food(id int4,favorite_food char(10));
create table color(id int4,color char(10));
copy food from stdin;
1
2 eggrolls
3 ice cream
4
\.
copy color from stdin;
1 red
2
3 blue
4
\.
copy people from stdin;
1 bob smith
2 tom jones
3 jane doe
4 mike porter
\.
select fname,lname,color,favorite_food from people,food,color where
people.id=food.id and people.id=color.id;
Udv: rpetike
>>>>> "Fran" == Fran Fabrizio <ffabrizio@mmrd.com> writes: Fran> This is a little off topic but this is the best source of SQL knowledge Fran> I know about so hopefully this will be interesting enough for someone to Fran> answer. :-) Fran> I've got the following tables: Fran> Table people Fran> id fname lname Fran> 1 bob smith Fran> 2 tom jones Fran> 3 jane doe Fran> 4 mike porter Fran> Table food Fran> id favorite_food Fran> 2 eggrolls Fran> 3 ice cream Fran> Table color Fran> id color Fran> 1 red Fran> 3 blue Fran> I want a query to produce the result set: Fran> fname lname favorite_color favorite_food Fran> bob smith red null Fran> tom jones null eggrolls Fran> jane doe blue ice cream Fran> mike porter null null Fran> I'm having lots of trouble getting the right result or knowing whether Fran> this is even a valid usage of outer joins. Can somebody show me a Fran> working query? Thanks! Got it on the first try: test=# select * from people natural left join color natural left join food; id | fname | lname | color | favorite_food ----+-------+--------+-------+--------------- 1 | bob | smith | red | 2 | tom | jones | | eggrolls 3 | jane | doe | blue | ice cream 4 | mike | porter | | (4 rows) That's presuming "id" matches "id" in each table, thus the easy use of the "natural" keyword. The "left join" is what gives you nulls on the right. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
I'm sort of a beginner myself so forgive me if this is wrong. But that solution sort of assumes that data is being put into the tables at the same time.
Wouldn't a better solution be to setup the first table like this.
people
------------------------------
| id | fname | lname | food_id | color_id |
------------------------------
| 1 | bob | smith | 2 | 3 |
------------------------------
Then you should be able to do a normal join type select and get the right results no matter what.
Risko Peter wrote:
Wouldn't a better solution be to setup the first table like this.
people
------------------------------
| id | fname | lname | food_id | color_id |
------------------------------
| 1 | bob | smith | 2 | 3 |
------------------------------
Then you should be able to do a normal join type select and get the right results no matter what.
Risko Peter wrote:
On Tue, 20 Nov 2001, Fran Fabrizio wrote:This is a little off topic but this is the best source of SQL knowledge
I know about so hopefully this will be interesting enough for someone to
answer. :-)
I've got the following tables:
Table people
id fname lname
1 bob smith
2 tom jones
3 jane doe
4 mike porter
Table food
id favorite_food
2 eggrolls
3 ice cream
Table color
id color
1 red
3 blue
I want a query to produce the result set:
fname lname favorite_color favorite_food
bob smith red null
tom jones null eggrolls
jane doe blue ice cream
mike porter null null
I'm having lots of trouble getting the right result or knowing whether
this is even a valid usage of outer joins. Can somebody show me a
working query? Thanks!Hi Fran!
I'm a beginner, and maybe I will misinform you, but I think in the above
case you want your tables being joined by the ID column. In that case you
_should_ have a row in your auxiliary tables (color, food) for every
occuring IDs in the main table. It will solve your problem:
---
drop table people;
drop table food;
drop table color;
create table people(id int4,fname char(10),lname char(10));
create table food(id int4,favorite_food char(10));
create table color(id int4,color char(10));
copy food from stdin;
1
2 eggrolls
3 ice cream
4
\.
copy color from stdin;
1 red
2
3 blue
4
\.
copy people from stdin;
1 bob smith
2 tom jones
3 jane doe
4 mike porter
\.
select fname,lname,color,favorite_food from people,food,color where
people.id=food.id and people.id=color.id;
Udv: rpetike
---------------------------(end of broadcast)---- -----------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
-- Brian Avis SEARHC Medical Clinic Juneau, AK 99801 (907) 463-4049 cd /pub more beer
It would probably be better to set it up like this, if that's what you're going to do: id person_id food_id color_id 1 1 2 3 etc... person 1 bob smith etc... this way if there are two people named bob smith you still can have a unique person. Otherwise your table design breaks down at that point. This is also probably the most normalized way to do it... Note also that using this method, if you need to change a name, food, or color, the changes will happen instantly across the whole table structure. --Russell ----- Original Message ----- From: "Brian Avis" <brian.avis@searhc.org> To: "Risko Peter" <rpetike@freemail.hu> Cc: "Fran Fabrizio" <ffabrizio@mmrd.com>; <pgsql-general@postgresql.org> Sent: Tuesday, November 20, 2001 9:13 AM Subject: Re: [GENERAL] Slightly OT: outer joins > I'm sort of a beginner myself so forgive me if this is wrong. But that > solution sort of assumes that data is being put into the tables at the > same time. > > Wouldn't a better solution be to setup the first table like this. > > people > > ------------------------------ > | id | fname | lname | food_id | color_id | > ------------------------------ > | 1 | bob | smith | 2 | 3 | > ------------------------------ > > > Then you should be able to do a normal join type select and get the > right results no matter what. > > > Risko Peter wrote: > > >On Tue, 20 Nov 2001, Fran Fabrizio wrote: > > > >>This is a little off topic but this is the best source of SQL knowledge > >>I know about so hopefully this will be interesting enough for someone to > >>answer. :-) > >>I've got the following tables: > >>Table people > >>id fname lname > >>1 bob smith > >>2 tom jones > >>3 jane doe > >>4 mike porter > >>Table food > >>id favorite_food > >>2 eggrolls > >>3 ice cream > >>Table color > >>id color > >>1 red > >>3 blue > >>I want a query to produce the result set: > >>fname lname favorite_color favorite_food > >>bob smith red null > >>tom jones null eggrolls > >>jane doe blue ice cream > >>mike porter null null > >>I'm having lots of trouble getting the right result or knowing whether > >>this is even a valid usage of outer joins. Can somebody show me a > >>working query? Thanks! > >> > >Hi Fran! > > > > I'm a beginner, and maybe I will misinform you, but I think in the above > >case you want your tables being joined by the ID column. In that case you > >_should_ have a row in your auxiliary tables (color, food) for every > >occuring IDs in the main table. It will solve your problem: > >--- > >drop table people; > >drop table food; > >drop table color; > >create table people(id int4,fname char(10),lname char(10)); > >create table food(id int4,favorite_food char(10)); > >create table color(id int4,color char(10)); > >copy food from stdin; > >1 > >2 eggrolls > >3 ice cream > >4 > >\. > >copy color from stdin; > >1 red > >2 > >3 blue > >4 > >\. > >copy people from stdin; > >1 bob smith > >2 tom jones > >3 jane doe > >4 mike porter > >\. > >select fname,lname,color,favorite_food from people,food,color where > > people.id=food.id and people.id=color.id; > > > > > >Udv: rpetike > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 3: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to majordomo@postgresql.org so that your > >message can get through to the mailing list cleanly > > > > -- > Brian Avis > SEARHC Medical Clinic > Juneau, AK 99801 > (907) 463-4049 > cd /pub > more beer > > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.298 / Virus Database: 161 - Release Date: 11/13/01 _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Thanks everyone for the replies and instruction. I agree that there are better ways to layout the tables, but I'm sort of the guy who gets handed the crappy poker hand while it's owner goes to the restroom and is expected to make it a winner. :-) In other words, I really can't lay out the tables the way I would've wanted to. So, within those contraints Randal hit it right on the nose. I was definitely not catching on enough to see that the 'NATURAL' option to the joins is an obvious choice, which was part/all of my problem. Thanks Randal and everyone! -Fran "Randal L. Schwartz" wrote: > Got it on the first try: > > test=# select * from people natural left join color natural left join food; > id | fname | lname | color | favorite_food > ----+-------+--------+-------+--------------- > 1 | bob | smith | red | > 2 | tom | jones | | eggrolls > 3 | jane | doe | blue | ice cream > 4 | mike | porter | | > (4 rows) > > That's presuming "id" matches "id" in each table, thus the easy > use of the "natural" keyword. The "left join" is what gives you > nulls on the right.
>>>>> "Fran" == Fran Fabrizio <ffabrizio@mmrd.com> writes: Fran> So, within those contraints Randal hit it right on the nose. I Fran> was definitely not catching on enough to see that the 'NATURAL' Fran> option to the joins is an obvious choice, which was part/all of Fran> my problem. Thanks Randal and everyone! [..] >> test=# select * from people natural left join color natural left join food; Well, even without the natural part of the join, let's presume that people.id needed to be matched to color.people_id, you could say that as: select id, fname, lname, color, favorite_food from people left join color on people.id = color.people_id left join food on people.id = food.people_id So the main clue wasn't the NATURAL, it was the LEFT JOIN, to give you the nulls for the table rows that didn't match. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!