RE: how to tell if something hasn't happened yet
Argh, that was not the one i wanted to send :) This is the one: select s.* from store s where s.id not in (select t.storeid from trans t where t.created > date(now()) - interval 1 day); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Tue, April 17, 2007 09:20, Mogens Melander wrote: > Arg, come on, really. > > where t.created <= date(now())); > > -- > Later > > Mogens Melander > +45 40 85 71 38 > +66 870 133 224 > > > On Mon, April 16, 2007 15:18, Jay Blanchard wrote: >> [snip] >> select s.* from store s >> where s.id not in >> (select t.storeid from trans t where t.created=date(now())); >> [/snip] >> >> This is close, but it does not exclude previous days. I only want to see >> those that have not logged in today. >> >> >> -- >> This message has been scanned for viruses and >> dangerous content by OpenProtect(http://www.openprotect.com), and is >> believed to be clean. >> >> > > > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet
Arg, come on, really. where t.created <= date(now())); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Mon, April 16, 2007 15:18, Jay Blanchard wrote: > [snip] > select s.* from store s > where s.id not in > (select t.storeid from trans t where t.created=date(now())); > [/snip] > > This is close, but it does not exclude previous days. I only want to see > those that have not logged in today. > > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet - SOLVED
[snip] [snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. [/snip] select store.storeid, store.stname from store where store.storeid not in ( select transaction.storeid from transaction where substring(transaction.created, 1, 10) > date_sub(current_date(), interval 1 day) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet
[snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to tell if something hasn't happened yet
How about this: select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Sat, April 14, 2007 00:22, Jay Blanchard wrote: > Good day gurus and gurettes! > > I have a table; > > | transactionid | int(11) | NO | PRI | | auto_increment | > | username | varchar(32) | NO | | || > | storeid | varchar(6) | NO | | || > | action| int(4) | NO | | || > | code | int(2) | NO | | || > | ipAddr| varchar(32) | NO | | || > | created | datetime| NO | MUL | || > | created_by| varchar(32) | NO | | || > > I used to have a query (I have misplaced it somehow) where I could tell > which storied had not logged in (created) today yet. No matter how hard > I try I cannot remember the query. > > What I need is a query that will tell me at any given point during the > day which storeid is not online (created). I do have a sister table > where all of the storeid's are, so the join happens there. I can test > created for IS NULL but it does not limit the query to today. > > select store.storeid, store.stName > from store left outer join transaction > on(store.storeid = transaction.storeid) > where transaction.created IS NULL > and store.active = 'yes' > group by store.storeid; > > How can I limit this to today only without having to hard code a date > into the query? > > TVMIA! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet
It depends on what's in the datetime column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Something like this: select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.active = 'yes' and date(transaction.created)=date(curdate()) group by store.storeid; -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 13, 2007 6:22 PM To: [EMAIL PROTECTED] Subject: how to tell if something hasn't happened yet Good day gurus and gurettes! I have a table; | transactionid | int(11) | NO | PRI | | auto_increment | | username | varchar(32) | NO | | || | storeid | varchar(6) | NO | | || | action| int(4) | NO | | || | code | int(2) | NO | | || | ipAddr| varchar(32) | NO | | || | created | datetime| NO | MUL | || | created_by| varchar(32) | NO | | || I used to have a query (I have misplaced it somehow) where I could tell which storied had not logged in (created) today yet. No matter how hard I try I cannot remember the query. What I need is a query that will tell me at any given point during the day which storeid is not online (created). I do have a sister table where all of the storeid's are, so the join happens there. I can test created for IS NULL but it does not limit the query to today. select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.active = 'yes' group by store.storeid; How can I limit this to today only without having to hard code a date into the query? TVMIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to tell if something hasn't happened yet
Good day gurus and gurettes! I have a table; | transactionid | int(11) | NO | PRI | | auto_increment | | username | varchar(32) | NO | | || | storeid | varchar(6) | NO | | || | action| int(4) | NO | | || | code | int(2) | NO | | || | ipAddr| varchar(32) | NO | | || | created | datetime| NO | MUL | || | created_by| varchar(32) | NO | | || I used to have a query (I have misplaced it somehow) where I could tell which storied had not logged in (created) today yet. No matter how hard I try I cannot remember the query. What I need is a query that will tell me at any given point during the day which storeid is not online (created). I do have a sister table where all of the storeid's are, so the join happens there. I can test created for IS NULL but it does not limit the query to today. select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.active = 'yes' group by store.storeid; How can I limit this to today only without having to hard code a date into the query? TVMIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
