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