Re: [SQL] Join-Question
От | De Moudt Walter |
---|---|
Тема | Re: [SQL] Join-Question |
Дата | |
Msg-id | 388CB16B.F8D7436D@planetinternet.be обсуждение исходный текст |
Ответ на | Join-Question ("Dorthe Luebbert" <dorthe@luebbert.net>) |
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: