SELECT from two tables... one to many relationship... can postgresql offer anything unique?
От | Alan T. Miller |
---|---|
Тема | SELECT from two tables... one to many relationship... can postgresql offer anything unique? |
Дата | |
Msg-id | 005301c4623e$c1f3a0c0$0a01a8c0@webdev обсуждение исходный текст |
Ответ на | Re: .pgpass (<ghaverla@freenet.edmonton.ab.ca>) |
Ответы |
Re: SELECT from two tables... one to many relationship... can postgresql offer anything unique?
|
Список | pgsql-novice |
I know what I want to do has been done a thousand times before, however I am looking for suggestions on the best way to go about it. I have two tables, one holds products, the other holds photos. There is a one to many relationship between products and photographs. For example... TABLE 1 (products): products.id products.title products.description TABLE 2 (product photos) photos.id photos.id_product photos.filename photos.height photos.width photos.position the 'position' field is used to determine which is the main photo (photos are ordered by their position). IN other words the photo with position 1 would be the main thumbnail. There are two things I would like to be able to get from a SELECT statement. The first is a list of products with the thumbnail info for the product. I immediately see two ways of doing this but am looking for a better solution. SOLUTION 1: select all products, and then loop through that result set and do another select to get the photo information on each iteration of the loop. This would be slow, and create unnecesary overhead on the database. I do not think this is a good solution. SOLUTION 2: select all products and then use subselects to get the photograph information. I do not like this solution either, as there seems to be no clear way to get multiple fields such as filename and height and width etc without many subselects, or perhaps a creative concatenation routine. SOLUTION 3: I am open to suggestions... please! The other main task I would like to accomplish is to be able to select all the product information for a single product and get all the photographs for the product as well. However, aside from using an array to return all photos in that array in the select for products, I cannot think of an efficient way to do this without issuing two queries (one to get the product, and one to get the photo). I am very interested to hear how others have tacked simular situations like this. Any help is greatly appreciated. Alan
В списке pgsql-novice по дате отправления: