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

[Matt Williams]

Can you give us some sql to populate the tables with data?

As Brian said above it's very hard to find a solution when you have no
data to work with.

Matt

On 6/15/05, Brian Cummiskey <Brian at hondaswap.com> wrote:
> 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...
> 
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>