[thelist] Re: MySQL two tables SELECT rows that aren't common to both tables
Matt Williams
maxwell.hung at gmail.com
Thu Jun 16 05:24:00 CDT 2005
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 !
>
More information about the thelist
mailing list