Re: [SQL] Join-Question

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.