Results 1 to 9 of 9

Thread: Query Help

  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Query Help

    I'm trying to create a query which will return the current dvd price and the previous dvd price from between date values.
    The prices uses a date checked column. Below is the structure of my prices table:

    PHP Code:
    price_id    |    store_id    |    disc_id        |        price_total        |        price_checked
    1                1                1                    19.99                    2009
    -02-01 19:26:52
    2                1                1                    19.99                    2009
    -02-02 19:10:14
    3                1                1                    15.99                    2009
    -02-03 19:26:52
    4                1                1                    12.99                    2009
    -02-04 19:10:14 
    My current query isn't working correctly.

    PHP Code:
    SELECT t1.store_id,t1.disc_id,t1.price_total,t1.price_checked FROM prices AS t1
    LEFT JOIN prices 
    AS t2 ON t1.price_checked>=t2.price_checked AND t1.disc_id=t2.disc_id 
    This is returning more values because is being looped in the select.

    What I'd like returning is:

    PHP Code:
    price_id    |    store_id    |    disc_id        |        price_total        |        price_checked
    4                1                1                    12.99                    2009
    -02-04 19:10:14
    3                1                1                    15.99                    2009
    -02-03 19:26:52 

    Any advice would gratefully appreciated.

    Thanks,
    Lee

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    use any of these and check r u getting ur requirement r not
    SELECT top 2 price_id,store_id,disc_id,price_total,price_checke d FROM prices order by price_checked desc

    declare @stdate datetime ,@enddate datetime
    select @stdate = '2009-2-3' , @enddate = '2009-2-5'

    SELECT price_id,store_id,disc_id,price_total,price_checke d FROM prices
    WHERE price_checked BETWEEN @stdate AND @enddate
    order by price_checked desc

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Mobile
    Any advice would gratefully appreciated.
    The following assumes the price_id's always go up by one and that there are no missing id's. I've used a PHP variable for the disk id. You mentioned you wanted the price from between date values but your SQL was puling the latest price and the price before it - I guessed you wanted the latest prices :

    Code:
    select p1.store_id,p1.disc_id,p1.price_total,p1.price_checked 
    from   prices p1
    where  p1.disc_id = $disk_id
           and p1.price_id >= (
                 select max( p2.price_id ) - 1
                 from   prices p2
                 where  p2.disc_id = p1.disk_id )
    If you did want the results for between two dates then I'd do it in a way similar to bklr's where the dates are PHP variables :
    Code:
    select p1.store_id,p1.disc_id,p1.price_total,p1.price_checked 
    from   prices p1
    where  p1.disc_id = $disk_id
           and p1.price_checked between $start_date and $end_date
    order by p1.price_checked desc
    limit 2

    Quote Originally Posted by bklr
    declare @stdate datetime ,@enddate datetime
    select @stdate = '2009-2-3' , @enddate = '2009-2-5'

    SELECT price_id,store_id,disc_id,price_total,price_checke d FROM prices
    WHERE price_checked BETWEEN @stdate AND @enddate
    order by price_checked desc
    Won't this code just produce all the prices between these dates? The variable syntax suggests Sybase / MS SQL rather than MySQL. Adding a rowcount would fix the number of dates returned
    Last edited by mike_bike_kite; 02-06-09 at 05:35.

  4. #4
    Join Date
    Feb 2009
    Posts
    4
    Code:
    select p1.store_id,p1.disc_id,p1.price_total,p1.price_checked 
    from   prices p1
    where  p1.disc_id = $disk_id
           and p1.price_id >= (
                 select max( p2.price_id ) - 1
                 from   prices p2
                 where  p2.disc_id = p1.disk_id )
    This query worked great but i forgot that when there is another store (store_id) it will only select 1 store, i'm looking at selecting multiply stores and select the highest price from that store between the dates.


    Lee

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I think you need to supply a few examples.

  6. #6
    Join Date
    Feb 2009
    Posts
    4
    My price table looks like this:
    PHP Code:
    price_id store_id disc_id price_total price_checked          
    1               1               1        19.99        2009
    -02-06 08:33:53   
    2               1               1        14.99        2009
    -02-08 08:34:01  
    3               1               1        12.99        2009
    -02-27 11:54:38 
    4               1               1        1.99         2009
    -02-28 14:15:47
    5               1               2        12.99        2009
    -02-06 15:38:28
    6               2               1        12.99        2009
    -02-06 17:39:07 
    7               2               1        10.99        2009
    -02-06 17:39:16 
    I would like the query to return the total price between the newest date but there could be many stores in the price table;

    would like it like the example below:

    PHP Code:
    store_id disc_id price_total price_checked
    1             1          12.99          2009
    -02-27 11:54:38
    1             1          1.99            2009
    -02-28 14:15:47
    2             1          12.99          2009
    -02-06 17:39:07
    2             1          10.99          2009
    -02-06 17:39:16 

    Many thanks,
    Lee

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Without creating your tables and inserting all your data it's difficult to test on my side - something like this might do it but you can quickly find out :
    Code:
    select p1.store_id,p1.disc_id,p1.price_total,p1.price_checked 
    from   prices p1
    where  p1.disc_id = $disk_id
           and p1.price_id >= (
                 select max( p2.price_id ) - 1
                 from   prices p2
                 where  p2.disc_id = p1.disk_id
                        and p2.store_id = p1.store_id )

  8. #8
    Join Date
    Feb 2009
    Posts
    4
    That query worked but only when the store_id where next to together like 1,1,2,2,3,3,4,4
    If the store_id went 1,2,3,4,1,2,3,4 the query would only return one row.

    Thanks,
    Lee

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Mobile
    That query worked but only when the store_id where next to together like 1,1,2,2,3,3,4,4
    Using your original data it produced exactly the results you wanted - as I understand it!

    Quote Originally Posted by Mobile
    If the store_id went 1,2,3,4,1,2,3,4 the query would only return one row.
    I believe the query will return 4 rows but whether these are the rows you want or not is difficult for me to guess

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •