> Consider following are data in each table > > Location : > id , name, code > 1, stock, stock > 2, customer, customer > 3, asset, asset > > Product : > id, name, code, location > 1, product1, p1, 1 > 2, product2, p2, 3 > > > Product_Move : > id, product_id, source_location, destination_location, datetime 1, 1, > Null, 1, 2012-10-15 10:00:00 2, 2, Null, 1, 2012-10-15 10:05:00 3, 2, > 1, 3, 2012-12-01 09:00:00 > > Please review all data , you can see, current location of product1 > (p1) is 1 (stock) and current location of product2 (p2) is 3 (asset). > > now i want to find location of all products for given period > > for example : 2012-11-01 to 2012-11-30, then i need result should be > like below move_id, product_id, location_id 1, 1, 1 2, 2, 1 > > another example : 2012-11-01 to 2012-12-31 move_id, product_id, > location_id 1, 1, 1 2, 2, 1 3, 2, 3 > > Now I really don't know how to do this. > > can you advise me more ? > > > Thanks, > > Dhaval
I think these are the sqls you are looking for: SELECT pm.id as move_id, p.id as product_id, l.id as location_id FROM product_move pm inner join product p on pm.product_id = p.id inner join location l on pm.destination_location = l.id and datetime BETWEEN '2010-1-01' AND '2012-12-31' Regards, Russell Keane INPS Follow us on twitter | visit www.inps.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
