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/
---------------------------------------------------------------------
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
