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 Link to comment https://forums.phpfreaks.com/topic/204084-select-query-help/ 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. Link to comment https://forums.phpfreaks.com/topic/204084-select-query-help/#findComment-1068925 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 Link to comment https://forums.phpfreaks.com/topic/204084-select-query-help/#findComment-1068999 Share on other sites More sharing options...
Canman2005 Posted June 7, 2010 Author Share Posted June 7, 2010 Sorted it! Link to comment https://forums.phpfreaks.com/topic/204084-select-query-help/#findComment-1069003 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.