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

Re: [SQL] Join-Question

From: <Borek(dot)Lupomesky(at)ujep(dot)cz>
To: Dorthe Luebbert <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-23 22:38:17
Message-ID: Pine.LNX.4.10.10001232333001.2771-100000@diaspar.ujep.cz (view raw or flat)
Thread:
Lists: pgsql-sql
-----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-----


In response to

pgsql-sql by date

Next:From: Mark StosbergDate: 2000-01-24 00:36:07
Subject: can I index a field of type "point"?
Previous:From: Dorthe LuebbertDate: 2000-01-23 18:38:55
Subject: Join-Question

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