Re: [SQL] Join-Question - Mailing list pgsql-sql

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 andpersons.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.