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: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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
   Simple example:

SELECT a.t1, a.t2 FROM table1 t1, table2 t2 WHERE a.t1 = b.t2
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:  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 ]===========
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see


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-2017 The PostgreSQL Global Development Group