Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Join-Question

From: De Moudt Walter <wdemoudt(at)planetinternet(dot)be>
To: dorthe(at)luebbert(dot)net
Cc: pgsql-sql(at)hub(dot)org, luebbert(at)globalpark(dot)de
Subject: Re: [SQL] Join-Question
Date: 2000-01-24 20:09:15
Message-ID: 388CB16B.F8D7436D@planetinternet.be (view raw or flat)
Thread:
Lists: 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 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.


In response to

pgsql-sql by date

Next:From: Alain TESIODate: 2000-01-24 20:58:12
Subject: Re: [SQL] Backend crash
Previous:From: Bruce MomjianDate: 2000-01-24 19:08:13
Subject: Re: [SQL] Backend crash

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group