> 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