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