From: De Moudt Walter <wdemoudt@planetinternet.be>
To: dorthe@luebbert.net
Cc: pgsql-sql@hub.org
Cc: luebbert@globalpark.de
Subject: Re: [SQL] Join-Question
Date: Mon, 24 Jan 2000 21:09:15 +0100
Message-ID: <388CB16B.F8D7436D@planetinternet.be> (raw)
References: <200001231739.MAA63767@hub.org>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wdemoudt@planetinternet.be, dorthe@luebbert.net, pgsql-sql@hub.org, luebbert@globalpark.de
Subject: Re: [SQL] Join-Question
In-Reply-To: <388CB16B.F8D7436D@planetinternet.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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