Re: [SQL] Join-Question

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