help me, pls

4ebura6ka

Hello,

I've such a problem:
There are 2 tables "products" and "prices"

products
pk_product_id | product_name

prices
fk_product_id | price1 | price2 | ins_date

In the table "products" I keep product information and in "prices" - its prices. Field "ins_date" allows me to have price history for all products.

The problem is, that I want to select max(ins_date) for current product and then select "price1" and "price2" for it.

peeter_talvistu

i don't really understand your problem, you seem to have answered it yourself.

however, is the column fk_product_id auto_incremented or does it show the pk_product_id of the corresponding product. there has to be a column in prices tabel that has the id-s of products.

4ebura6ka

No I can't format query correctly. For example I inserted one product's record into table "products". Then I want to get prices for it.
I wrote:

SELECT a.price1, a.price2
     FROM prices a, (SELECT max(ins_date) FROM prices WHERE fk_product_id = 1) b
          WHERE a.ins_date=b.ins_date and a.fk_product_id=1 and b.fk_product_id=1

But MySQL doesn't want to execute this query... 🙁

peeter_talvistu

i don't understand the example query you gave. it doesn't seem to fit the table structure you gave in your first post.

try to put it into two different queries. first take the max date and then use the result to formulate the second query. see i fyou can get it working this way.

dnszero

FYI, only the newest version of MySQL (4.1.0) will execute subquerries (ie, a SELECT inside of another SELECT).