[thelist] MySQL two tables SELECT rows that aren't common to bothtables

[Luther, Ron]

Simon Perry asked:

>>I have two MySQL tables with date ranges 

>>I want to return every row from [one table] for a specific date 
>>where that date does not also fall between a range of dates in 
>>[the other table] I can think of ways to do this with two calls 
>>and a bit of PHP but I would prefer and single more elegant 
>>MySQL solution.


Hi Simon,

I think you may be on the right track.  With the table structures 
both containing date ranges it certainly sounds like you need to 
do a bit of recursive looping.

Here's an alternative ... You could create a temp table to simplify 
the problem.

Take a record from your rates table:
ID      P_ID    START_DATE       END_DATE      RATE
1        33     2005-01-12      2005-01-15      799

... and rewrite it as four records in your temp table:

P_ID    RATE_DATE       RATE
 33     2005-01-12      799
 33     2005-01-13      799
 33     2005-01-14      799
 33     2005-01-15      799


Now you're only looking at a 

SELECT temp.P_ID, temp.RATE_DATE, temp.RATE 
FROM temp, full_dates
WHERE temp.P_ID = full_dates.P_ID
AND temp.RATE_DATE NOT BETWEEN(full_dates.START_DATE,
full_dates.END_DATE)

[or whatever the equivalent MySQL syntax might be.]


HTH,

RonL.