Hi. Ok, i get it. Here is one method, but i'm afraid it may not work in
mysql.
select t1.name,count(*) as nb 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 AND nb =
(DAYOFYEAR("2001-02-06")-DAYOFYEAR("2001-02-04")) GROUP BY t1.id;
----- Original Message -----
From: Web Depressed <[EMAIL PROTECTED]>
To: Rus <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, February 05, 2001 12:39 PM
Subject: Re: Can anyone do this ?
> 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
