Re: SELECT from two tables... one to many relationship... can postgresql offer anything unique?

Alan T. Miller wrote:
> [...]
> 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.

You basically want to JOIN the tables, I think.

This is from the top of my head in the early morning
without any coffee, so please excuse any stupid syntax
errors ...

SELECT pr.*, ph.* FROM products AS pr, photos AS ph
WHERE pr.id = ph.id_product and ph.position = 1;

That will give you one row for each product, along with
the photo information for the first thumbnail.

> [...]
> 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.

SELECT pr.*, ph.* FROM products AS pr, photos AS ph
WHERE pr.id = ph.id_product and pr.id == your_desired_product_id;

That will give you one row for each photo which belongs to
your_desired_product_id, as well as that product's data
(which the same for every row).

Please note that the may well be more efficient ways to
perform those joins (and I'm sure someone else will
mention them). But basically the above should work.

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"That's what I love about GUIs: They make simple tasks easier,
and complex tasks impossible."
-- John William Chambless