Jump to content

SELECT Query Help


Canman2005

Recommended Posts

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.