Hi Rus,
I'm not sure I follow, but the DATE column represents
the dates at which I have a certain item in stock.
ie (. for from 2001-02-04 -> 2001-02-06 I have Item1
in stock. However I only have Item2 in stock on
2001-02-04 AND 2001-02-06).
A typical application would be a car hire company:
where the Items represent a particular car model. My
user requests a list of all cars available from
2001-02-04 to 2001-02-06. I can therefor only return
Item1 as Item2 is not available on 2001-02-05.
I hope this answers your question.
Thanks,
-- Frank
--- Rus <[EMAIL PROTECTED]> wrote:
> 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
>
__________________________________________________
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
