Canman2005 Posted June 7, 2010 Share Posted June 7, 2010 HI all I wonder if you can help. I have 2 database tables, which look like; ---products--- `id`,`title`,`price` 1 ipod 9.99 2 mp3 99.99 ---archive--- `id`,`product_id`,`price` 1 1 19.99 2 1 5.00 3 2 15.00 The first table `products` shows my items for sale ------------ The second table `archive` shows what the products previous prices were, so your see that I have a field called `product_id`, and in that field, two of the rows are '1', so that related to row 1 of the `products` table (ipod) and the other rows values is '2', so that is related to row 2 in the `products` table (mp3). This basically stores the past prices of products, so that shows that the; 'ipod' was at somepoint in the past 19.99 and also 5.00, and the; 'mp3' at somepoint in the past was 15.00 ------------ Ok, I have a simple QUERY at the moment which looks like SELECT p.title, p.price FROM products p WHERE p.title = $_GET['q'] This returns ipod 9.99 mp3 99.99 My question is, how can I extend my SELECT QUERY but add to the WHERE statement something which would also return any rows which have been more expensive in the past than they currently are, so I guess that would need to check the `archive` table to find the prices of the products in the past. So with my data "mp3" would not be returned, as it is currently 99.99 and in the past it was 15.00, so its not been reduced in price at anytime "ipod" would be returned though, as it is currently 9.99 and in the past it was 19.99, so that shows it was reduced in price. Can anyone help? Been working all night to try and figure out and going to jump out the window at somepoint LOL Thanks everyone Dave Quote Link to comment Share on other sites More sharing options...
fenway Posted June 7, 2010 Share Posted June 7, 2010 First, you need to JOIN the tables; then you can filter out the rows you don't want. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted June 7, 2010 Author Share Posted June 7, 2010 Could you help a little more? I've tried so many options but none seem to work thanks Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted June 7, 2010 Author Share Posted June 7, 2010 Sorted it! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.