>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
