Thread: Join-Question
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?? Thanx so much for your help Dorthe
-----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-----
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??
>
> Thanx so much for your help
>
> Dorthe
>
> ************
Hi,
Here's how i did it : (sorry; long message)
These are the tables : (all fields are varchar() )
select * from hobbydata;
hobby_id|hobbyname
--------+--------------- 3|trumpet playing 2|coocking 1|relativating 4|carrot hunting
(4 rows)
select * from persondata;
person_id|firstname|lastname
---------+---------+-------- 1|Jan |Claessen 2|Hannibal |Lecter 3|Albert |Einstein
4|Jack |Rabbit
(4 rows)
select * from persons;
person_id|hobby_id
---------+-------- 1| 1 1| 3 3| 1 2| 2 4| 4
(5 rows)
select * from hobbydata, persondata, persons where persons.hobby_id = hobbydata.hobby_id andpersons.person_id =
persondata.person_id;
hobby_id|hobbyname |person_id|firstname|lastname|person_id|hobby_id
--------+---------------+---------+---------+--------+---------+-------- 3|trumpet playing| 1|Jan
|Claessen| 1| 3 1|relativating | 1|Jan |Claessen| 1| 1 2|coocking
| 2|Hannibal |Lecter | 2| 2 1|relativating | 3|Albert |Einstein| 3| 1
4|carrot hunting | 4|Jack |Rabbit | 4| 4
(5 rows)
# now the view :
create view whodoeswhat as select hobbydata.hobby_id, hobbyname,persondata.person_id, firstname, lastname from
hobbydata,persondata,persons where persons.hobby_id = hobbydata.hobby_id and persons.person_id = persondata.person_id;
select * from whodoeswhat;
hobby_id|hobbyname |person_id|firstname|lastname
--------+---------------+---------+---------+-------- 3|trumpet playing| 1|Jan |Claessen
1|relativating | 1|Jan |Claessen 2|coocking | 2|Hannibal |Lecter 1|relativating |
3|Albert |Einstein 4|carrot hunting | 4|Jack |Rabbit
(5 rows)
select firstname, lastname from whodoeswhat
where hobbyname = 'relativating';
firstname|lastname
---------+--------
Jan |Claessen
Albert |Einstein
(2 rows)
Seems to work :-)
May seem extensive work, but it's not THAT much, and afterwards you can
rely on one view that contains all information.