Can you explain what difference between dates 2001-02-04 and 2001-02-06 for
item2 and 2001-02-04 and 2001-02-06 for item1?
----- Original Message -----
From: Web Depressed <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, February 04, 2001 11:41 PM
Subject: Can anyone do this ?
> 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
>
>
---------------------------------------------------------------------
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
