Sir, in the first SQL statement, you could use
HAVING cnt = (DAYOFYEAR(min)-DAYOFYEAR(max)) + 1
Of course, this won't work if min is in the year 2000 and max is in
2001. I think this will actually be easier to work out in your
client, assuming you have a programmable client.
>Hi Bob,
>Many thanks for that. I was hoping (through absolute
>naiveity) not to have to go and calculate the number
>of days with the specified range. Unless, of course
>I could have mysql do this for me.
>
>Can anyone tell me how I could generate such a list.
>
>(ie something like:
>SELECT BETWEEN "2001-02-04" AND "2001-02-06"
> ????? )
>
>Thanks again,
>-- Frank
>
> > >Hi,
> > >I've been trying for a while now and I can't figure
> > >out how to do the following:
> > >
> > >In regular English, I wish to show all items for
> > which
> > >I have allocations greater than one for all days
> > >encapsulated between the user input range.
> > >
> > >User Input:
> > >-----------
> > >Date_from (ie. 2001-02-04)
> > >Date_to (ie. 2001-02-06)
> > >
> > >My Tables:
> > >----------
> > >Table1
> > >+----+--------+
> > >| id | name |
> > >+----+--------+
> > >| 1 | Item1 |
> > >| 2 | Item2 |
> > >+----+--------+
> > >
> > >Table2
> > >+------------+-------------+-----------+
> > >| date | no_of_items | table1_id |
> > >+------------+-------------+-----------+
> > >| 2001-02-04 | 3 | 1 |
> > >| 2001-02-05 | 2 | 1 |
> > >| 2001-02-06 | 2 | 1 |
> > >| 2001-02-04 | 2 | 2 |
> > >| 2001-02-06 | 1 | 2 |
> > >+------------+-------------+-----------+
> > >
> > >Basically my user says he/she needs a list of what
> > >items are available FROM a certain date TO a
> > certain
> > >date. I therefor need to return all items which I
> > >have at least 1 of for EACH of the dates within my
> > >users range:
> > >
> > >I have tried using BETWEEN but it fails (ie.
> > >If my user says: from 2001-02-04 to 2001-02-06,
> > using
> > >BETWEEN MySQL returns Item2 even though I do not
> > have
> > >Item 2 listed for 2001-02-05.
> > >
> > >Here is where I'm at:
> > >mysql> SELECT t1.*, t2.*
> > > -> FROM Table1 t1, Table2 t2
> > > -> WHERE t2.date BETWEEN "2001-02-04" AND
> > >"2001-02-06"
> > > -> AND t1.id=t2.table1_id
> > > -> AND t2.no_of_Items >=1;
> > >
> >
> >+----+-------+------------+-------------+-----------+
> > >| id | Item | date | no_of_items | table1_id
> > |
> >
> >+----+-------+------------+-------------+-----------+
> > >| 1 | Item1 | 2001-02-04 | 3 | 1
> > |
> > >| 1 | Item1 | 2001-02-05 | 2 | 1
> > |
> > >| 1 | Item1 | 2001-02-06 | 2 | 1
> > |
> > >| 2 | Item2 | 2001-02-04 | 2 | 2
> > |
> > >| 2 | Item2 | 2001-02-06 | 1 | 2
> > |
> >
> >+----+-------+------------+-------------+-----------+
> > >
> > >What I need is a query which will generate the
> > >following result:
> > >
> >
> >+----+-------+------------+-------------+-----------+
> > >| id | Item | date | no_of_items | table1_id
> > |
> >
> >+----+-------+------------+-------------+-----------+
> > >| 1 | Item1 | 2001-02-04 | 3 | 1
> > |
> > >| 1 | Item1 | 2001-02-05 | 2 | 1
> > |
> > >| 1 | Item1 | 2001-02-06 | 2 | 1
> > |
> >
> >+----+-------+------------+-------------+-----------+
> > >
> > >Forgive me if this is actually simple and/or I
> > haven't
> > >explained clearly.
> > >
> > >Many thanks in advance for your help.
> > >
> > >-- Frank
> >
> > Sir, create a variable day_count with the count of
> > days in the
> > user-supplied range. Then set up the following temp
> > table.
> >
> > CREATE TEMPORARY TABLE counts
> > SELECT table1_id, Count(DISTINCT date_field) as
> > cnt
> > WHERE data_field BETWEEN min AND max
> > GROUP BY id
> > HAVING cnt = day_count;
> >
> > Now you have a table with the IDs of items that
> > occur at least once
> > each day in the user-supplied range. You don't need
> > the DISTINCT if
> > each item can only have one record per day. Now some
> > inner joins
> > should get the results you want.
> >
> > SELECT table1.*, table2.*
> > FROM table1, table2, counts
> > WHERE table1.id = table2.table1_id AND table1.id
> > = counts.table1_id
> > AND date_field BETWEEN min AND max;
> >
> > I haven't actually run this, which means that
> > there's probably a
> > mistake or three.
> >
> > Bob Hall
> >
> > Know thyself? Absurd direction!
> > Bubbles bear no introspection. -Khushhal Khan
> > Khatak
> >
> >
>---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list
> > archive)
> >
> > To request this thread, e-mail
> > <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> >
><[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try:
> > http://lists.mysql.com/php/unsubscribe.php
> >
>
>
>__________________________________________________
>Get personalized email addresses from Yahoo! Mail - only $35
>a year! http://personal.mail.yahoo.com/
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
