Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts

    using returned var in subquery to do more in sub query

    Hi,

    I am running a sub query which has to do a 'thing'.

    when that 'thing' is done, I need then to perform another 'thing' so, the second 'thing' is conditional on the outcome of the first.

    I have been working on this main query for about 2 weeks now so I am getting desperate.

    here's the query with some pseudo code where I am stuck
    Code:
    AND pl.start_date IN (
          SELECT max(live.start_date)
          FROM product_live live
          WHERE start_date <= ?  #$arrival_date_requested  
          AND live.id=pl.id
          AND business_id = ?    -- successful thus far
    
                   THEN
                if (live.end_date <= ?) #$departure -- trying to get the end_date from the same record as in 1st part
                select min(live.start_date)
                  from product_live AS live
                where end_date >= ?  #$arrival_date_requested 
           
                   AND live.id=pl.id
                   AND business_id = ?
                   )
    for clarity: the relevant table cols are like this
    | start_date | end_date | price |

    I need help with the red bit because whether it runs, is determined by the value returned by the first part of the sub query.

    any pointers at all would be most welcome.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Ok... so... you want to find all rows that are fully encompassed in the start-end date range of other rows? For example if the "main" date range is March 24 - March 27, you want to find any rows that contain a date range of, say, March 25-26, or March 24-25, or March 25-27, but you want to exclude ranges of March 23-25 and March 26-28. Is this correct?

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Thanks for the rpely.

    Not quite what I am looking for...

    In the first part of the sub query, I am getting the start_date that is the highest one earlier than arrival date. That provides me with the tariff for this part of the stay. But, if the end date (of that same record) is earlier than the departure date, I need to get another record - next seasons details and prices.

    The reason is that I am trying to enable bookings which bridge different years/seasons and which therefore have different prices.

    Is it possible to do this in mysql or do I need to group concat and then post process the results to get what I need?

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

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