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

[Brian Cummiskey]

Simon Perry wrote:
> Brian Cummiskey wrote:
> 
>> 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
>>
> That will be 4.1 that has the nested query/sub-select then and I'm 
> running 4.0.23 :-( Good thought though I'll get onto the server 
> maintainers and see what the likelihood of an upgrade would be.

never fear--  an inline select can be done with a join as well--  its 
just not as clean.

first some semi-pseduocode...  change what you want to some query text:

return every row from RATES

 > select * from RATES AS R

for a specific date where that date does not also fall between a range 
of dates in FULL_DATES

 > WHERE R.P_ID not IN (select F.P_ID from FULL_DATES where $test_date 
between F.START_DATE and F.END_DATE)


Now, this won't work because you can't do inline selects with your version.

so we need to change this to a join.

SELECT * FROM RATES AS R
LEFT OUTER JOIN FULL_DATES AS F
ON F.P_ID = R.P_ID
WHERE $test_date between F.START_DATE and F.END_DATE

but this won't work either, because we need NOT BETWEEN instead, and 
frankly, i don't know how to do that.  lol  Maybe someone else cna pick 
up where i left off.  I would need the tables/data to play with to 
actually come up with something.  sitting blind is hard.

HTH none the less...