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

[Brian Cummiskey]

Simon Perry wrote:
> Hi,
> 
> I have two MySQL tables with date ranges in, one for rates and one for 
> full dates
> 
> RATES
> ID   P_ID START_DATE   END_DATE     RATE
> 1        33     2005-11-12      2005-12-12      799
> 
> FULL_DATES
> ID   P_ID START_DATE   END_DATE
> 1         33  2005-11-19        2005-11-26
> 
> I want to return every row from RATES for a specific date where that 
> date does not also fall between a range of dates in FULL_DATES. 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.
> 


What version of mysql are you using?  this can be done easily with a 
nested query/sub-select