Обсуждение: Join-Question

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, 23 Jan 2000, 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??
  Unfortunately, PostgreSQL doesn't support outer joins. The suggested
workaround is to do normal inner join and merge it (through UNION clause)
with a query where you select entries with given attribute having NULL
value.  Simple example:

SELECT a.t1, a.t2 FROM table1 t1, table2 t2 WHERE a.t1 = b.t2
UNION
SELECT a.t1, NULL FROM table1 t1, table2 t2 WHERE t2.a IS NULL;
  (I hope I haven't mistyped something).
  Bye Borek

- --

=====================================================================
BOREK LUPOMESKY, network administrator    University of J. E. Purkyne                                         Ceske
mladeze8
 
WWW:       http://www.ujep.cz/~lupomesk/  Usti nad Labem, 40096
IRCnet:    Borek @ #usti                  The Czech Republic
PGP keyid: B6A06AEB                       tel: +420-602-376368
==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE4i4LfA6dWI7agausRApygAKCqYkW+mK5y3L/8tTRXQI5JZMEFBACgp28x
YMXelzl02S5F2D5VDlrTOgc=
=qKB8
-----END PGP SIGNATURE-----

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.