Re: Can anyone do this ?
Hi: the way I handle this situation is: "create temporary table if not exists tblName (somefld I need, etc.) select iptable.* (or field names) from myTable where bluemoon = yes" Works every time for me. You need to know that temporary tables are only available to the creating user during the session in which it is created (you can't find it with freemascon or any other tool that I am aware of) after the session is completed the temporary table is poofware. hope this helps. Jack :-)= - Original Message - From: "Web Depressed" <[EMAIL PROTECTED]> To: "Bob Hall" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, February 14, 2001 4:46 PM Subject: Re: Can anyone do this ? > Hi Bob/ All, > > I cannot get the creation of the TEMPORARY Table to > work: > The select statement works fine: > mysql> SELECT table1_id, Count(date) as c FROM Table2, > Table1 > -> WHERE Table2.date BETWEEN "2001-02-02" AND > "2001-02-06" > -> AND Table2.table1_id=Table1.id > -> GROUP BY Table1.id HAVING c=3; > +---+---+ > | table1_id | c | > +---+---+ > | 1 | 3 | > +---+---+ > 1 row in set (0.02 sec) > > mysql> CREATE TEMPORARY TABLE counts > -> SELECT table1_id, Count(date) as c FROM Table2, > Table1 > -> WHERE Table2.date BETWEEN "2001-02-02" AND > "2001-02-06" > -> AND Table2.table1_id=Table1.id > -> GROUP BY Table1.id HAVING c=3; > > > If I manually create this counts table and work with > that I do get the desired output: > mysql> CREATE TABLE count (table1_id VARCHAR(10), c > VARCHAR(10)); > Query OK, 0 rows affected (0.06 sec) > > mysql> insert into count VALUES (1,3); > Query OK, 1 row affected (0.01 sec) > > mysql> select * from count; > +---+--+ > | table1_id | c| > +---+--+ > | 1 | 3| > +---+--+ > 1 row in set (0.00 sec) > > mysql> SELECT Table1.*, Table2.* > -> FROM Table1, Table2, count > -> WHERE Table1.id=Table2.table1_id AND Table1.id > = count.table1_id > -> AND Table2.date BETWEEN "2001-02-04" AND > "2001-02-06"; > ++---++-+---+ > | id | Item | date | no_of_items | table1_id | > ++---++-+---+ > | 1 | Item1 | 2001-02-04 | 2 | 1 | > | 1 | Item1 | 2001-02-05 | 2 | 1 | > | 1 | Item1 | 2001-02-06 | 2 | 1 | > ++---++-+---+ > 3 rows in set (0.02 sec) > > > Do you have any idea where I'm going wrong ? > > Kind Regards, > -- 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 > > > __ > Do You Yahoo!? > 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
Re: Can anyone do this ?
Sir, 99% of the time, if someone can't create a TEMPORARY table, it means that they're using an earlier version of MySQL that doesn't support temp tables. The best thing to do is to update to the most recent version. If you can't update (e.g. if MySQL is being maintained by a web hosting service), then you will have to create a persistant table, and then delete it before ending your session. If you're going to be using the table frequently, you can leave it in your database and just empty it before or after each use. Bob Hall >Hi Bob/ All, > >I cannot get the creation of the TEMPORARY Table to >work: >The select statement works fine: >mysql> SELECT table1_id, Count(date) as c FROM Table2, >Table1 > -> WHERE Table2.date BETWEEN "2001-02-02" AND >"2001-02-06" > -> AND Table2.table1_id=Table1.id > -> GROUP BY Table1.id HAVING c=3; >+---+---+ >| table1_id | c | >+---+---+ >| 1 | 3 | >+---+---+ >1 row in set (0.02 sec) > >mysql> CREATE TEMPORARY TABLE counts > -> SELECT table1_id, Count(date) as c FROM Table2, >Table1 > -> WHERE Table2.date BETWEEN "2001-02-02" AND >"2001-02-06" > -> AND Table2.table1_id=Table1.id > -> GROUP BY Table1.id HAVING c=3; > > >If I manually create this counts table and work with >that I do get the desired output: >mysql> CREATE TABLE count (table1_id VARCHAR(10), c >VARCHAR(10)); >Query OK, 0 rows affected (0.06 sec) > >mysql> insert into count VALUES (1,3); >Query OK, 1 row affected (0.01 sec) > >mysql> select * from count; >+---+--+ >| table1_id | c| >+---+--+ >| 1 | 3| >+---+--+ >1 row in set (0.00 sec) > >mysql> SELECT Table1.*, Table2.* > -> FROM Table1, Table2, count > -> WHERE Table1.id=Table2.table1_id AND Table1.id >= count.table1_id > -> AND Table2.date BETWEEN "2001-02-04" AND >"2001-02-06"; >++---++-+---+ >| id | Item | date | no_of_items | table1_id | >++---++-+---+ >| 1 | Item1 | 2001-02-04 | 2 | 1 | >| 1 | Item1 | 2001-02-05 | 2 | 1 | >| 1 | Item1 | 2001-02-06 | 2 | 1 | >++---++-+---+ >3 rows in set (0.02 sec) > > >Do you have any idea where I'm going wrong ? > >Kind Regards, >-- 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 > > >__ >Do You Yahoo!? >Get personalized email addresses from Yahoo! Mail - only $35 >a year! http://personal.mail.yahoo.com/ 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
Re: Can anyone do this ?
Hi Bob/ All, I cannot get the creation of the TEMPORARY Table to work: The select statement works fine: mysql> SELECT table1_id, Count(date) as c FROM Table2, Table1 -> WHERE Table2.date BETWEEN "2001-02-02" AND "2001-02-06" -> AND Table2.table1_id=Table1.id -> GROUP BY Table1.id HAVING c=3; +---+---+ | table1_id | c | +---+---+ | 1 | 3 | +---+---+ 1 row in set (0.02 sec) mysql> CREATE TEMPORARY TABLE counts -> SELECT table1_id, Count(date) as c FROM Table2, Table1 -> WHERE Table2.date BETWEEN "2001-02-02" AND "2001-02-06" -> AND Table2.table1_id=Table1.id -> GROUP BY Table1.id HAVING c=3; If I manually create this counts table and work with that I do get the desired output: mysql> CREATE TABLE count (table1_id VARCHAR(10), c VARCHAR(10)); Query OK, 0 rows affected (0.06 sec) mysql> insert into count VALUES (1,3); Query OK, 1 row affected (0.01 sec) mysql> select * from count; +---+--+ | table1_id | c| +---+--+ | 1 | 3| +---+--+ 1 row in set (0.00 sec) mysql> SELECT Table1.*, Table2.* -> FROM Table1, Table2, count -> WHERE Table1.id=Table2.table1_id AND Table1.id = count.table1_id -> AND Table2.date BETWEEN "2001-02-04" AND "2001-02-06"; ++---++-+---+ | id | Item | date | no_of_items | table1_id | ++---++-+---+ | 1 | Item1 | 2001-02-04 | 2 | 1 | | 1 | Item1 | 2001-02-05 | 2 | 1 | | 1 | Item1 | 2001-02-06 | 2 | 1 | ++---++-+---+ 3 rows in set (0.02 sec) Do you have any idea where I'm going wrong ? Kind Regards, -- 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 __ Do You Yahoo!? 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
Re: Can anyone do this ?
Web Depressed wrote: > Hi Bob, > Many thanks for that. I was hoping (through absolute > naiveity) not to have to go and calculate the number > of days with the specified range. Unless, of course > I could have mysql do this for me. Question: why are you using reserved words for your column names? -- http://www.pricegrabber.com | Dog is my co-pilot.
Re: Can anyone do this ?
Sir, in the first SQL statement, you could use HAVING cnt = (DAYOFYEAR(min)-DAYOFYEAR(max)) + 1 Of course, this won't work if min is in the year 2000 and max is in 2001. I think this will actually be easier to work out in your client, assuming you have a programmable client. >Hi Bob, >Many thanks for that. I was hoping (through absolute >naiveity) not to have to go and calculate the number >of days with the specified range. Unless, of course >I could have mysql do this for me. > >Can anyone tell me how I could generate such a list. > >(ie something like: >SELECT BETWEEN "2001-02-04" AND "2001-02-06" > ? ) > >Thanks again, >-- Frank > > > >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/
RE: Can anyone do this ?
Ooops, I forgot to notice that you required consequetive days. This query works for me: select T1.*, T2.*, count(T2.id) as c, concat(T2.id,T2.d) as x from table1 as T1, table2 as T2, table 2 as T3 where T1.id=T2.id and T2.id=T3.id and T2.d>='2001-02-04' and T2.d<='2001-02-06' and T3.d>='2001-02-04' and T3.d<='2001-02-06' group by x having c=3; Uglier, but seems to work ok. Of course, you'll have to change the having c=3 part to refect the number of days in the interval. Or maybe there is a mysql date subtraction routine for that... >>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 | >>++---++-+---+ _ Do You Yahoo!? Get your free @yahoo.com address at http://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
Re: Can anyone do this ?
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 |
> > >
> >
>
Re: Can anyone do this ?
Hi Bob, Many thanks for that. I was hoping (through absolute naiveity) not to have to go and calculate the number of days with the specified range. Unless, of course I could have mysql do this for me. Can anyone tell me how I could generate such a list. (ie something like: SELECT BETWEEN "2001-02-04" AND "2001-02-06" ? ) Thanks again, -- Frank > >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 > __ 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.my
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: ht
Re: 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 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
Re: Can anyone do this ?
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
RE: Can anyone do this ?
Try: select t1.*, t2* from table1 as t1, table2 as t2 where t2.date>='2001-02-04' and t2.date<='2001-02-06' and t1.id=t2.id and t2.no_of_items>=1 >>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; Jeff _ Do You Yahoo!? Get your free @yahoo.com address at http://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
