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