using returned var in subquery to do more in sub query

[bazz]

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

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

[Fumigator]

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?

[bazz]

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