Thanks Niphlod (By the way I thought I have answered but can't find my answer so if I duplicate the entry sorry)
I had never worked with PostgreSQL nor any other compliant engine. I have the "bad habits" of MySQL and SQLite. So I was expecting the same behavoir. Below is the sample data, thanks for the help: Table price_lists id | name 1 | Default price 2 | Discount price Table products id | name | description 1 | Product One | Really cool description 2 | Product Two | Another product Table product_prices id | product id | price list id | price 1 | 1 | 1 | 10.00 2 | 1 | 2 | 8.50 3 | 2 | 1 | 15.00 4 | 2 | 2 | 13.00 Table product images id | product id | image 1 | 1 | path to first image for product one 2 | 1 | path to second image for product one 3 | 1 | path to third image for product one 4 | 2 | path to only image for product two And the query must return all the products that have a price in the selected price list (sent to the query), the product price (for the price list) and only one image (or Null if no image is set for this product in particular). The query as I had it worked in MySQL and SQLite Sample query results would be: product id | product name | product description | price | image 1 | Product one | Really cool description | 10.00 | path to first image for product one 2 | Product two | Another product | 15.00 | path to only image for product two If there is a better way to store/retrieve all this data I'm open to suggestions. In the begginng I was doing a query for all products (with pagination) and then for every product I would do a second query to get the price and image. But I tried to come up with a single query. Thanks for the help. El miércoles, 1 de enero de 2014 07:43:41 UTC-6, Niphlod escribió: > > this example lacks "consistency", and engines that are forgiving about SQL > standards such as mysql and sqlite just make everything worse. > Grouping in sql means what it means in english: you take a "universe" of n > records and you'd like represent it in groups . Grouping doesn't "change" > the "universe". > That's why compliant engines (such as postgresql) allow in the selected > fields of a group(ed) query to be either the group(ed) fields or > aggregates of the remaining fields (such as sum(), avg(), min(), etc). > Could you post an example with data that you have in the tables and what > data do you want back ? > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
