-----BEGIN PGP SIGNED MESSAGE-----
On Sun, 23 Jan 2000, Dorthe Luebbert wrote:
> 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
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).
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
-----END PGP SIGNATURE-----
In response to
pgsql-sql by date
|Next:||From: Mark Stosberg||Date: 2000-01-24 00:36:07|
|Subject: can I index a field of type "point"?|
|Previous:||From: Dorthe Luebbert||Date: 2000-01-23 18:38:55|