RE: sql help: delete row where only related to one other row
Take a look at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.ht ml CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 20 February 2008 12:44 To: mysql@lists.mysql.com Subject: sql help: delete row where only related to one other row Say I have two tables: table_a -- a_id (primary key) b_id table_b -- b_id (primary key) name there is a one to many mapping between rows in table b and rows in table a. Say I had an Id of a row in table a an (a_id, say 5). Now, what I want to do is delete the row in table_a (easy enough), but I also want to delete the related row in table_b, if it is ONLY related to the a_id of 5. In other words, I want to delete the row from table b, but I don't want to delete a row from table b that is in use by another row in table a. I'm thinking some type of subquery could do this, but I'm not sure. Can some one tell me how to do this? -- 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]
sql help: delete row where only related to one other row
Say I have two tables: table_a -- a_id (primary key) b_id table_b -- b_id (primary key) name there is a one to many mapping between rows in table b and rows in table a. Say I had an Id of a row in table a an (a_id, say 5). Now, what I want to do is delete the row in table_a (easy enough), but I also want to delete the related row in table_b, if it is ONLY related to the a_id of 5. In other words, I want to delete the row from table b, but I don't want to delete a row from table b that is in use by another row in table a. I'm thinking some type of subquery could do this, but I'm not sure. Can some one tell me how to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL help/problem with timestamped data differences
Hi All I have the following data example UID Timestamp 123456 20071201 12:00:01 123456 20071201 12:00:06 987654 20071201 12:00:01 987654 20071201 12:00:09 etc I need : UID Timestamp secs 123456 20071201 12:00:01 123456 20071201 12:00:06 0005 987654 20071201 12:00:01 987654 20071201 12:00:09 0008 or similar solution. I am using version 5.0 and willing to use interim tables or any SQL based technique. Thanks in advance Mark -- Mark Carson Managing Integrated Product Intelligence CC EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help/problem with timestamped data differences
Mark, is the 'secs' column the offset from the minimum value of the timestamp column? If so, you might try something like this: SELECT UNIX_TIMESTAMP(MIN(timestamp)) INTO @min_timestamp FROM my_table; SELECT uid, timestamp, UNIX_TIMESTAMP(timestamp) - @min_timestamp AS secs FROM my_table ORDER BY 1, 2, 3; HTH, Dan On Jan 8, 2008 7:17 PM, mark carson [EMAIL PROTECTED] wrote: Hi All I have the following data example UID Timestamp 123456 20071201 12:00:01 123456 20071201 12:00:06 987654 20071201 12:00:01 987654 20071201 12:00:09 etc I need : UID Timestamp secs 123456 20071201 12:00:01 123456 20071201 12:00:06 0005 987654 20071201 12:00:01 987654 20071201 12:00:09 0008 or similar solution. I am using version 5.0 and willing to use interim tables or any SQL based technique. Thanks in advance Mark -- Mark Carson Managing Integrated Product Intelligence CC EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex sql help
I have a need to output a recordset that shows the record with the higest value for severity within a date range. That is easy enough, but, in the same query, I need to show that data 3 times in the same query. Once where event_avail = 1, then again where event_perf = 1 and finally where even_sec = 1. It can be across any hostid as long as it is in the groupid list as seen below. I am including an example of what looks like it works for ONE of the sets of data (see e.event_avail = 1) but I need to include a severity, hostid, color, severitydesc, fontcolor, eventid and description for the most servere event in that group where event_perf = 1 and again for wehre event_sec = 1... This is grouped by Groupid so I can show the most severe event that has happened for availability, performance and security within that group (across any host in the group). Here is a sample that shows me avail data correctly (I think) select g.name AS name, g.groupid AS groupid, e.severity AS apoint, e.hostid AS ahostid, fs.color AS apointcolor, fs.severitydesc AS apointdesc, fs.fontcolor AS apointfont, e.eventid, e.description FROM groups g, fs_events e, fs_severity fs, hosts_groups hg WHERE e.hostid = hg.hostid and g.groupid = hg.groupid and fs.severityid = e.severity and e.event_avail = 1 and e.time_stamp = date_sub(now(), interval 30 DAY) and e.acknowledged in (0,1) and g.groupid in (2,3,4,5,6) group by groupid order by name,apoint desc Here are the tables involved: CREATE TABLE `groups` ( `groupid` bigint(20) unsigned NOT NULL default '0', `name` varchar(64) NOT NULL default '', PRIMARY KEY (`groupid`), KEY `groups_1` (`name`), KEY `groupid` (`groupid`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `hosts_groups` ( `hostgroupid` bigint(20) unsigned NOT NULL default '0', `hostid` bigint(20) unsigned NOT NULL default '0', `groupid` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`hostgroupid`), KEY `hosts_groups_groups_1` (`hostid`,`groupid`), KEY `hostid` (`hostid`,`groupid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `fs_severity` ( `severityid` int(4) NOT NULL, `severitydesc` varchar(64) NOT NULL, `color` varchar(64) NOT NULL, `fontcolor` varchar(64) NOT NULL, `severityabbrev` varchar(64) default NULL, `severityclass` varchar(64) default NULL, PRIMARY KEY (`severityid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `fs_events` ( `eventid` int(4) NOT NULL auto_increment, `hostid` int(4) NOT NULL, `expression` varchar(255) default NULL, `description` varchar(255) default NULL, `remediation` varchar(255) default NULL, `status` int(4) NOT NULL, `value` int(4) default NULL, `severity` int(4) NOT NULL, `time_stamp` datetime default NULL, `event_type` int(4) NOT NULL, `src_addr` varchar(64) NOT NULL default '', `dest_addr` varchar(64) default NULL, `service_type` varchar(64) default NULL, `event_avail` int(11) NOT NULL, `event_perf` int(11) NOT NULL, `event_sec` int(11) NOT NULL, `itemid` int(4) NOT NULL, `triggerid` int(4) NOT NULL, `devicetype` int(11) default NULL, `acknowledged` int(11) NOT NULL, `comment` varchar(4000) default NULL, `last_changed` datetime default NULL, `username` varchar(255) default NULL, `url` varchar(255) default NULL, PRIMARY KEY (`eventid`,`src_addr`), KEY `hostid` (`hostid`), KEY `severity` (`severity`), KEY `time_stamp` (`time_stamp`), KEY `triggerid` (`triggerid`), KEY `hostid_2` (`hostid`,`severity`,`time_stamp`,`triggerid`), KEY `hostid_3` (`hostid`,`status`,`severity`) ) ENGINE=MyISAM AUTO_INCREMENT=20967 DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a lil sql help please.
Hi, I have the following Query and Im a lil lost on this one SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, tbe_orders.order_piececount FROM tbe_orders The query produces the following results: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +1 + + oid1 + 2006-07-08 +2 + + oid1 + 2006-07-08 +3 + + oid5 + 2006-07-08 +7 + + oid5 + 2006-07-08 +1 + + oid4 + 2006-07-08 +1 + + oid4 + 2006-07-08 +2 + + oid4 + 2006-07-08 +1 + +++ This is actually right. However, ideally what I'm wanting is this: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +6 + + oid5 + 2006-07-08 +8 + + oid4 + 2006-07-08 +4 + +++ Note the order_piececount column. What do I need to do to my SQL statement to perform this action ? My guess that I need to perform a secondary query inside the statement to get the computed value of order_piececount. Anyone ? M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a lil sql help please.
At 18:51 9.7.2006, you wrote: Hi, I have the following Query and Im a lil lost on this one SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, tbe_orders.order_piececount FROM tbe_orders SELECT tbe_orders.order_id, tbe_orders.order_date, sum(tbe_orders.order_piececount) FROM tbe_orders GROUP BY tbe_orders.order_id or SELECT tbe_orders.order_id, tbe_orders.order_date, sum(tbe_orders.order_piececount) FROM tbe_orders GROUP BY tbe_orders.order_id, tbe_orders.order_date depending whether date matters or not. Regards, Dundo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a lil sql help please.
m i l e s wrote: Hi, I have the following Query and Im a lil lost on this one SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, tbe_orders.order_piececount FROM tbe_orders The query produces the following results: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +1 + + oid1 + 2006-07-08 +2 + + oid1 + 2006-07-08 +3 + + oid5 + 2006-07-08 +7 + + oid5 + 2006-07-08 +1 + + oid4 + 2006-07-08 +1 + + oid4 + 2006-07-08 +2 + + oid4 + 2006-07-08 +1 + +++ This is actually right. However, ideally what I'm wanting is this: +++ + order_id + order_date + order_piececount + +++ + oid1 + 2006-07-08 +6 + + oid5 + 2006-07-08 +8 + + oid4 + 2006-07-08 +4 + +++ Note the order_piececount column. What do I need to do to my SQL statement to perform this action ? My guess that I need to perform a secondary query inside the statement to get the computed value of order_piececount. Anyone ? M i l e s. SELECT DISTINCT tbe_orders.order_id, tbe_orders.order_date, SUM(tbe_orders.order_piececount ) FROM tbe_orders GROUP BY order_id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A bit of SQL help for a MySQL novice.
Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. Thank you. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A bit of SQL help for a MySQL novice.
Rick Dwyer [EMAIL PROTECTED] wrote on 11/17/2005 10:28:51 AM: Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. Thank you. Rick The problem is, you have dirty data making it very hard to use it as a search target. There are different ways of handling this: a) scrub your data (preferably during input or import) so that all phone numbers are stored in the exact same pattern b) store each number as its component parts (country code, city/area code, exchange, circuit) c) add a field of scrubbed data to your existing table and populate it. d) add fields for each part of the phone number to your tables and populate them. e) handle all searching and substring matching outside of SQL. Options a) and c) require that a clean copy of the data be stored in the database. That means that you pick a pattern and make all of your numbers look like that pattern. If, for instance, you get just the number 555-1212 you would need to generate something like 'x-xxx-555-1212' as a replacment (where the x is used to indicate missing information). This is slower to search on because you have to do substring matches but since phone numbers are already organized from least-specific to most-specific (left to right) it's already optimized for some types of substring matches. Options b) and d) provide the ability to index each part of a phone number. Here is an example breakdown: 1 (203) 555-1212 - country code:1, citycode:203, exchange: 555, circut: 1212 If any part of a number is missing, you can use a NULL value for that part... 555-1212 - country code: NULL, citycode: NULL, exchange: 555, circut: 1212 This would be all integer comparisons, VERY fast to search. If you have millions of phone numbers or international phone numbers, consider this schema. It may be hard to determine the foreign `exchange` and `circuit` parts. In that case just pick either the `exchange` or `circuit` field and put that whole portion of it in there. This takes more time to set up but is MUCH faster to search (how do you think the phone company does it?) Options b) and d) also allow you to have searchable data while preserving the original information. Option e) has the most flexibility but takes the database server out of the loop, which will destroy your search performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: A bit of SQL help for a MySQL novice.
- Original Message - From: Rick Dwyer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest of the number, and the extension (if any), you _could_ provide a separate field in your input form for each of those things. However, you don't have to do it that way; you could just as well put the full phone number in one input field and then split it out when you insert it into the database. That's up to you. But I would definitely use the input routines to force the phone numbers to follow whatever pattern you want it to have. It shouldn't be the database's job to handle this sort of thing, at least in my opinion. Of course, you'll want to fix the data that is already in the database, too. (If there are only a few rows in the table, you could do that manually. If not, you could write SQL to do it.) However, if you insist on allowing multiple formats for your phone numbers, the String Functions in MySQL should help you. Just look for them in the manual: http://dev.mysql.com/doc/refman/4.1/en/index.html (chapter 12). You may have to use a combination of functions to create new temporary versions of the phone number that don't have the punctuation but you can probably manage something, although it might be ugly. Another possibility is that you could write a user-defined function to strip the punctuation out of the phone numbers. See http://dev.mysql.com/doc/refman/4.1/en/adding-functions.html for information about this. Basically, you would write your own function in C or C++ to do this work for you, then plug the function into MySQL so that you can use it in your SQL. For instance, if you wrote a function called STRIP_PHONE_PUNCTUATION() and installed it in MySQL, your query would look like this: 'Select mid(STRIP_PHONE_PUNCTUATION(phone, 1,4)) as phoneareacode from phonetable' Your new function would give you a version of the phone number that had no punctuation, then the mid() function would find the area code
Re: A bit of SQL help for a MySQL novice.
Unfortunately, the phone numbers come from text logs that get imported into mysql. Because the phone number is often displayed on a document for the customer, they will dictate how they want it to appear (i.e. with ( ) etc.). The phone logs simply record those values as they are so data will be entered in an unclean manner. Therefore I must deal with it on the backend. Thanks for the pointers. Rick On Nov 17, 2005, at 11:15 AM, Rhino wrote: - Original Message - From: Rick Dwyer [EMAIL PROTECTED] link.com To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest of the number, and the extension (if any), you _could_ provide a separate field in your input form for each of those things. However, you don't have to do it that way; you could just as well put the full phone number in one input field and then split it out when you insert it into the database. That's up to you. But I would definitely use the input routines to force the phone numbers to follow whatever pattern you want it to have. It shouldn't be the database's job to handle this sort of thing, at least in my opinion. Of course, you'll want to fix the data that is already in the database, too. (If there are only a few rows in the table, you could do that manually. If not, you could write SQL to do it.) However, if you insist on allowing multiple formats for your phone numbers, the String Functions in MySQL should help you. Just look for them in the manual: http://dev.mysql.com/doc/refman/4.1/en/ index.html (chapter 12). You may have to use a combination of functions to create new temporary versions of the phone number that don't have the punctuation but you can probably manage something, although it might be ugly. Another possibility is that you could write a user-defined function to strip the punctuation out of the phone numbers. See http:// dev.mysql.com/doc/refman/4.1/en/adding-functions.html
Re: A bit of SQL help for a MySQL novice.
The fact that the data is coming from the text logs doesn't really change anything; _something_ is generating the text logs so that something could be changed to force users to supply phone numbers in whatever format you want. Of course, that doesn't mean _you_ can force those changes to take place; if the text logs are coming from a customer, you may not be able to persuade them to change the way they generate the logs. Your boss may not even want you to explore the possibility with the customer for fear of ruffling feathers with the customer(s). If that is the case, I'd suggest writing a UDF (user-defined function) to do the stripping of the punctuation for you, unless you can finagle the existing MySQL functions to do the work for you. I'm surprised by how few string functions MySQL supports. I use DB2 most of the time and it has lots and lots of built-in functions, many of which are dedicated to string manipulation. If you can't keep the punctuation out of the data in the first place and you can't figure out how strip the punctuation with the existing MySQL functions, I'd say a UDF is pretty much the _only_ way to get that area code. Rhino - Original Message - From: Rick Dwyer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 11:53 AM Subject: Re: A bit of SQL help for a MySQL novice. Unfortunately, the phone numbers come from text logs that get imported into mysql. Because the phone number is often displayed on a document for the customer, they will dictate how they want it to appear (i.e. with ( ) etc.). The phone logs simply record those values as they are so data will be entered in an unclean manner. Therefore I must deal with it on the backend. Thanks for the pointers. Rick On Nov 17, 2005, at 11:15 AM, Rhino wrote: - Original Message - From: Rick Dwyer [EMAIL PROTECTED] link.com To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest
Re: A bit of SQL help for a MySQL novice.
Rick
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
You need an unpunct() function. Not available in 4 or 5, easy to write
in 5.0 as a stored function, not hard to add as a 'C' udf in 4.1 if you
write 'C'. Since it's a common requirement, likely someone has written
it. Failing that, you may be stuck with the absurd
replace(replace(replace(replace(replace(@s,'(',''),')',''),'
',''),'-',''),'.','').
PB
-
Rick Dwyer wrote:
Hello All.
I am hoping for a bit of help with some code that has really given me
some trouble. If this is not he correct forum for this any help in
pointing me to a more suited list would be appreciated.
I have a MySQL 4.1.x database containing records with phone numbers.
Most of the phone numbers are enter in 12035551212 format, but some
are entered with spaces or - or ( or other characters.
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
Is there a way to have the select statement examine only numeric
values in the phone number so it would disregard the other charcters?
In Lasso, you can use a Replace with a Regular Expression function to
have just the digits 0-9 examined but haven't been able find a way
to do this in SQL.
Any help is appreciated.
Thank you.
Rick
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A bit of SQL help for a MySQL novice.
Hi Rick,
Below are some MySQL functions that might help with your problem.
Sorry, for not much of direct answer.
But the idea is that you can combine some of these (string) functions -
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html to parse
out the string you are looking for. If you are programming in one of
Unix, you could also pull your answer into UNIX script to parse it out
what ever you need.
I.
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of
CONCAT(). The first argument is the separator for the rest of the
arguments. The separator is added between the strings to be
concatenated. The separator can be a string as can the rest of the
arguments. If the separator is NULL, the result is NULL. The function
skips any NULL values after the separator argument.
mysql SELECT CONCAT_WS(',',
'First name','Second name','Last Name');
- 'First name,Second name,Last Name'
mysql SELECT CONCAT_WS(',','First name',NULL,'Last Name');
- 'First name,Last Name'
Before MySQL 4.0.14, CONCAT_WS() skips empty strings as well as NULL
values.
II.
FIND_IN_SET(str,strlist)
FIND_IN_SET(str,strlist)
Returns a value 1 to N if the string str is in the string list strlist
consisting of N substrings. A string list is a string composed of
substrings separated by `,' characters. If the first argument is a
constant string and the second is a column of type SET, the
FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if
str is not in strlist or if strlist is the empty string. Returns NULL if
either argument is NULL. This function will not work properly if the
first argument contains a comma (`,') character.
mysql SELECT FIND_IN_SET('b','a,b,c,d');
- 2
III.
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in
string str. This is the same as the two-argument form of LOCATE(),
except that the arguments are swapped.
mysql SELECT INSTR('foobarbar', 'bar');
- 4
mysql SELECT INSTR('xbar', 'foobar');
- 0
This function is multi-byte safe. In MySQL 3.23, this function is case
sensitive. For 4.0 on, it is case sensitive only if either argument is a
binary string.
IV.
LEFT(str,len)
LEFT(str,len)
Returns the leftmost len characters from the string str.
mysql SELECT LEFT('foobarbar', 5);
- 'fooba'
V
LENGTH(str)
LENGTH(str)
Returns the length of the string str, measured in bytes. A multi-byte
character counts as multiple bytes. This means that for a string
containing five two-byte characters, LENGTH() returns 10, whereas
CHAR_LENGTH() returns 5.
mysql SELECT LENGTH('text');
- 4
Mikhail Berman
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 17, 2005 1:51 PM
To: Rick Dwyer
Cc: mysql@lists.mysql.com
Subject: Re: A bit of SQL help for a MySQL novice.
Rick
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
You need an unpunct() function. Not available in 4 or 5, easy to write
in 5.0 as a stored function, not hard to add as a 'C' udf in 4.1 if you
write 'C'. Since it's a common requirement, likely someone has written
it. Failing that, you may be stuck with the absurd
replace(replace(replace(replace(replace(@s,'(',''),')',''),'
',''),'-',''),'.','').
PB
-
Rick Dwyer wrote:
Hello All.
I am hoping for a bit of help with some code that has really given me
some trouble. If this is not he correct forum for this any help in
pointing me to a more suited list would be appreciated.
I have a MySQL 4.1.x database containing records with phone numbers.
Most of the phone numbers are enter in 12035551212 format, but some
are entered with spaces or - or ( or other characters.
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
Is there a way to have the select statement examine only numeric
values in the phone number so it would disregard the other charcters?
In Lasso, you can use a Replace with a Regular Expression function to
have just the digits 0-9 examined but haven't been able find a way to
do this in SQL.
Any help is appreciated.
Thank you.
Rick
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date:
11/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com
Re: SQL help for qty Sold YTD...
I spent several minutes looking at your question and your data model and nothing jumped out at me that precluded you from determining the quantity of the this vendor's items sold via this data model. I might have missed something though. I'm a little concerned that your LineItem table appeared to have no primary key. In my opinion, the primary key of a line item table should be an order number (saleTranID?) and then a sequence number (1 for the first item on the order, 2 for the second, etc.) but you (apparently) have no primary key defined at all and don't have a sequence number either. However, that shouldn't keep this particular query from running or returning appropriate rows. I am also assuming that invID is an inventory ID - my brain kept reading it as invoice ID but I learned to ignore it ;-) - where an inventory ID uniquely identifies one product that you sell, e.g. invID 1 might be power supplies for Sony camcorders while invID 2 might be Palm Tungsten E PDAs. This is something I would normally call a product ID if I were doing the naming :-) If, in fact, invID *is* an invoice ID, i.e. something that uniquely identifies a particular sales transaction then there is something wrong which might explain why you're not getting any data. So, assuming I haven't misunderstood anything or simply missed something, I would be inclined to break the query down into chunks. Execute each chunk on its own andmake sure that each chunk delivers what you think it should. If it doesn't, either the query is wrong or the data isn't what you think it is. Verify that the data you expect is there by doing SELECTs against the relevant tables; if the data is there, it's got to be your query that is wrong. Inspect each chunk until you find the culprit(s) in either the SQL or the data. Also, for what it's worth, I would strongly suggest that you set up a test environment with a SMALL quantity of data in each table - 50 rows or less should be plenty for most situations - and try your queries against that test environment. That makes the testing process a lot less painful - why wait for many seconds or even minutes for the query to give you the wrong answer? - and let's you solve the problem faster. It might sound like a lot of work but it shouldn't be; just clone the real tables and then copy a small but representative sample of data from the real tables into the clones. You also asked about performance but there is no way anyone can comment on that without knowing a lot more about what indexes you have and, perhaps, which engine you are using. But, in my opinion, your first effort should be directed toward getting the query running correctly, THEN worry about making it go faster. Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 04, 2005 12:28 AM Subject: SQL help for qty Sold YTD... I cannot figure this one out. I have a Category table with 50,000 records, an Inventory table with over 2 million records. A Sales table with 500,000 records. And a LineItem table with 800,000 records pairing the Inventory ID with the Sales Transaction ID and Quantity. I need to generate a Quantity sold year to date for a certain vendor. The vendor code can be found in the Category table which has a relationship with Inventory. I am trying a SQL statement like this: select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID IN (select invID from Inventory where categoryid IN (select categoryid from Category where vendcode='AA')) this yields null when I know there are sales for that vendor in 2005. Simplified schemas for the tables are as follows: Category: ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | vendcode | char(3) | YES | MUL | NULL| | | categoryID | int(10) unsigned | | PRI | NULL| auto_increment | ++--+--+-+-+ + Inventory: +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | categoryID | int(11) | YES | MUL | NULL| | | invID| int(10) | | PRI | 0 | | | itemnum | int(11) | YES | MUL | NULL| | +--+---+--+-+-+---+ Sales: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | saletranID | int(10) unsigned | | PRI | NULL| auto_increment
Re: SQL help for qty Sold YTD...
I would first try refactoring your SQL to use INNER JOIN statements instead of the comma separated lists you are currently using. I would also not use any subqueries. Test this and see if it works for you: SELECT SUM(li.quantity) as qtysoldytd FROM LineItem li INNER JOIN Sales sa on li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005 INNER JOIN Inventory inv on inv.invID = li.invID INNER JOIN Category cat on cat.categoryid = inv.categoryid AND cat.vendcode='AA'; The linkages work like this: 1) LineItem links into Sales through saletranID and YEAR(solddate) 2) Sales links into Inventory through the invID 3) Inventory links to Category through categoryid and vendcode Because I used INNER JOINs, each link in the chain must exist across all tables or the row cannot be added to the final results. Because this query contains several joins and your table sizes are not insignificant it becomes a candidate for what I call piecewize evaluation. Piecewize evaluation is where you take the full query and build your desired results in stages. One stage that jumps out at me is the conversion of vendcode to a list of invID values. Another stage could be isolating just those line items for 2005. I suggest this because JOINing two tables (either by explicit declaration as I do or by comma-separated lists as you did) is a geometrically expensive operation (it's cost to compute grows by multiplying how many rows are participating from each table). If we start with two tables M and N and they each have m and n rows in them, a JOIN operation takes on the order of m*n cycles to compute. If we can somehow shrink each table participating in the JOIN (by pre-selecting certain rows) so that we now have m/4 and n/2 rows to JOIN that reduces your overall cost to (m * n)/8. When we are discussing products of m*n on the order of 100 million rows or so, reducing production time by a factor of 8 is noticable. The situation is even more apparent if you add more tables. Consider if you had tables A, B, and C and they had a,b, and c rows in them. If you had to JOIN those three tables to build a query it would take a*b*c units of time to complete. If we were only able to reduce each table by 10%, that reduces the overall computation to (.9*a)*(.9*b)*(.9*c) = .729(abc) If: a = 50,000 b = 500,000 c = 800,000 records The original execution cost is proportional to: (5 * 50 * 80) = 2 (2.0e16) after 10% reductions through precomputations: 2.0e16 * .729 = 1.458e16 --- # of rows combinations NOT fed through the CPU to be evaluated as being in the result or not: 2.0e16 - 1.458e16 = 5.42e+15 = 5420 How long do you think it takes even a modern computer to do 5420 tests? It can make a serious difference. Piecewize evaluation works VERY WELL in stored procedures (if you are on v5.0 or higher) because you can parameterize your queries quite easily and you are assured of executing the same query pattern every time you need it. ## stage 1 - identifying Line items from 2005 CREATE TEMPORARY TABLE tmpLI ( KEY(invID) ) SELECT li.invID, li.quantity FROM LineItem li INNER JOIN Sales sa on li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005 ## stage 2 - identifying Inventory Items for a certain category CREATE TEMPORARY TABLE tmpInv ( KEY(invID) ) SELECT DISTINCT invID FROM Inventory inv on inv.invID = li.invID INNER JOIN Category cat on cat.categoryid = inv.categoryid AND cat.vendcode='AA'; ## stage 3 - compute your desired results SELECT SUM(li.quantity) FROM tmpLI li INNER JOIN tmpInf inv ON inv.invID = li.invID; ## stage 4 - the database is not your momma. Clean up after yourself... DROP TEMPORARY TABLE tmpLi; DROP TEMPORARY TABLE tmpInv; ## end query I hope that helps (HTH), Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 11/04/2005 12:28:50 AM: I cannot figure this one out. I have a Category table with 50,000 records, an Inventory table with over 2 million records. A Sales table with 500,000 records. And a LineItem table with 800,000 records pairing the Inventory ID with the Sales Transaction ID and Quantity. I need to generate a Quantity sold year to date for a certain vendor. The vendor code can be found in the Category table which has a relationship with Inventory. I am trying a SQL statement like this: select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID IN (select invID from Inventory where categoryid IN (select categoryid from Category where vendcode='AA')) this yields null when I know there are sales for that vendor in 2005. Simplified schemas for the tables are as follows: Category:
SQL help for qty Sold YTD...
I cannot figure this one out. I have a Category table with 50,000 records, an Inventory table with over 2 million records. A Sales table with 500,000 records. And a LineItem table with 800,000 records pairing the Inventory ID with the Sales Transaction ID and Quantity. I need to generate a Quantity sold year to date for a certain vendor. The vendor code can be found in the Category table which has a relationship with Inventory. I am trying a SQL statement like this: select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID IN (select invID from Inventory where categoryid IN (select categoryid from Category where vendcode='AA')) this yields null when I know there are sales for that vendor in 2005. Simplified schemas for the tables are as follows: Category: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | vendcode | char(3) | YES | MUL | NULL| | | categoryID | int(10) unsigned | | PRI | NULL| auto_increment | ++--+--+-+-++ Inventory: +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | categoryID | int(11) | YES | MUL | NULL| | | invID| int(10) | | PRI | 0 | | | itemnum | int(11) | YES | MUL | NULL| | +--+---+--+-+-+---+ Sales: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | saletranID | int(10) unsigned | | PRI | NULL| auto_increment | | solddate | datetime | YES | | NULL| | +--+--+--+-+-++ LineItem: ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | invID | int(10) | YES | MUL | NULL| | | quantity | int(10) | YES | | NULL| | | saletranID | int(10) | YES | MUL | NULL| | ++-+--+-+-+---+ Can anybody shed some light on this and if this is even possible. I have indexes in place and the query is still slow to pull. Thanks a million, Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL help: Updating Strange Chrs.
Hi, I have an odd situation where I was handed just bad data, and while I have cleaned it up to the best of my ability one hurdle remains. I have a situation where I have the following example in a field: Canna ÒBengalÒ. Note the odd chrs Ò in the field ? I need to get rid of those, my question is HOW. And I have thousands of rows like this and its just ONE field that's the stumbling block. Any suggestions ? M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help: Updating Strange Chrs.
m i l e s wrote: Hi, I have an odd situation where I was handed just bad data, and while I have cleaned it up to the best of my ability one hurdle remains. I have a situation where I have the following example in a field: Canna ÒBengalÒ. Note the odd chrs Ò in the field ? I need to get rid of those, my question is HOW. And I have thousands of rows like this and its just ONE field that's the stumbling block. Any suggestions ? M i l e s. replace() eg: update some_table set some_field = replace( my_field, Ò, O ); -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
SQL help
Greetings, I have a table in my database called item. It has two fields: item and description. Unfortunately the item field has got data with quotes around it. IE KP-00310. I need to clean up the data and have the field contain just KP-00310. Since I have 10,000 records, I need a update statement or something to clean that up. Any ideas ? thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL help
[snip] Unfortunately the item field has got data with quotes around it. IE KP-00310. I need to clean up the data and have the field contain just KP-00310. Since I have 10,000 records, I need a update statement or something to clean that up. Any ideas ? [/snip] http://dev.mysql.com/doc/mysql/en/string-functions.html the manual is your friend UPDATE item SET item = REPLACE(item, '', '') -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
Darryl, Unfortunately the item field has got data with quotes around it. IE "KP-00310". I need to clean up the data and have the field contain just KP-00310. Since I have 10,000 records, I need a update statement or something to clean that up. To remove all double quotes: UPDATE tablename SET item=REPLACE(item,'"',''); PB - Darryl Hoar wrote: Greetings, I have a table in my database called item. It has two fields: item and description. Unfortunately the item field has got data with quotes around it. IE "KP-00310". I need to clean up the data and have the field contain just KP-00310. Since I have 10,000 records, I need a update statement or something to clean that up. Any ideas ? thanks, Darryl No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.14/79 - Release Date: 8/22/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
On 6/26/05, 2wsxdr5 wrote: Can someone tell me why this query works... SELECT UserKey FROM( SELECT UserKey, Count(GiftKey) Gifts FROM Gift Group BY UserKey ) GC WHERE GC.Gifts = 3 Why this construction and not simply: SELECT UserKey FROM Gift GROUP BY UserKey HAVING Count(GiftKey) = 3 And this one doesn't? SELECT UserKey, UserID, FROM User WHERE UserKey IN (SELECT UserKey FROM( SELECT UserKey, Count(GiftKey) Gifts FROM Gift Group BY UserKey ) GC WHERE GC.Gifts = 3 ) How do you mean doesn't work? Does it give an unexpected result or an error? User info about the users UserKey is the key Gift list of gifts each user has on their wish list foreign key is UserKey Event ---gift giving events for users. foreign key is UserKey Emails email addresses users have sent a message to about their wish list. UserKey is the foreign key here too. The relationship between user and the other 3 tables is a 1 to many. I have the following query that I need to adjust some. SELECT u.UserKey, UserID, Count(distinct g.GiftKey) gifts, Count(distinct ev.EventKey) events, Count(distinct e.Email) Emails FROM User u NATURAL LEFT JOIN Gift g LEFT JOIN Emails e ON e.Userkey = u.UserKey LEFT JOIN GiftGivingEvent ev ON ev.UserKey = u.UserKey GROUP BY UserID What I really want is only the users where the gifts count is 3, the Event count is 1, the Emails count is 5 and and only count emails if e.Verified is = 1 I think you mean the following: SELECT * FROM User INNER JOIN ( SELECT UserKey, Count(UserKey) AS gifts FROM Gift GROUP BY UserKey HAVING Count(UserKey) 3 ) USING (Userkey) INNER JOIN ( SELECT UserKey, Count(UserKey) AS events FROM Event GROUP BY UserKey HAVING Count(UserKey) 1 ) USING (Userkey) INNER JOIN ( SELECT UserKey, Count(UserKey) AS emails FROM Emails WHERE Verified = 1 GROUP BY UserKey HAVING Count(UserKey) 5 ) USING (Userkey) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL help
Can someone tell me why this query works... SELECT UserKey FROM( SELECT UserKey, Count(GiftKey) Gifts FROM Gift Group BY UserKey ) GC WHERE GC.Gifts = 3 And this one doesn't? SELECT UserKey, UserID, FROM User WHERE UserKey IN (SELECT UserKey FROM( SELECT UserKey, Count(GiftKey) Gifts FROM Gift Group BY UserKey ) GC WHERE GC.Gifts = 3 ) In case you need some back ground and want to know what I eventually want to get read on... I have a DB of Users of my wish list site. The tables I have are User info about the users UserKey is the key Gift list of gifts each user has on their wish list foreign key is UserKey Event ---gift giving events for users. foreign key is UserKey Emails email addresses users have sent a message to about their wish list. UserKey is the foreign key here too. The relationship between user and the other 3 tables is a 1 to many. I have the following query that I need to adjust some. SELECT u.UserKey, UserID, Count(distinct g.GiftKey) gifts, Count(distinct ev.EventKey) events, Count(distinct e.Email) Emails FROM User u NATURAL LEFT JOIN Gift g LEFT JOIN Emails e ON e.Userkey = u.UserKey LEFT JOIN GiftGivingEvent ev ON ev.UserKey = u.UserKey GROUP BY UserID What I really want is only the users where the gifts count is 3, the Event count is 1, the Emails count is 5 and and only count emails if e.Verified is = 1 I am pretty sure I have to write code to do the last part with the emails but is there a way to do the part with the gift and event counts? -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL help
Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL help
correction in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- 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]
RE: SQL help
Properly, NULL values should be matched with 'foo IS NULL', as opposed to 'foo = NULL' which, by standard definition, always returns false regardless of the value of foo - michael dykman On Mon, 2005-02-28 at 16:02, Rob Brooks wrote: correction in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
NULL is an unknown value. Consequently, you cannot compare NULLs the way you expect. Effectively, = NULL is always false. Instead of items_online.ID = NULL you have to use items_online.ID IS NULL Michael Rob Brooks wrote: correction in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
In article [EMAIL PROTECTED], Rob Brooks [EMAIL PROTECTED] writes: The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 ... Nope. items_online.ID IS NULL for that record, but comparing anything = NULL ain't true, even for NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery SQL help
I tried what's below, seemed OK, so I replaced an IN for the = in
the subquery below because of the subquery's error message. I thought
w/IN I'd get three (3) records returned as expected. But when I tried
IN I got my entire DB returned - I don't show that below - it kept
scrolling so I aborted it. What did I do wrong? Seems like the inner
just returns three recs. and should match-up w/the outer for three recs.
too. I'm using 4.1. Thanks, any help arrpeciated. Lee G.
mysql SELECT access_no FROM balloon_txt WHERE MATCH
(access_no,recs_txt) AGAINST ('robin');
++
| access_no |
++
| BT-1037.11 |
| BT-2540|
| BT-1034.06 |
++
3 rows in set (0.01 sec)
mysql SELECT * FROM balloon_rec WHERE access_no='BT-1034.06';
+++++---+
| access_no | title | author
| doc_date | elec_access
|
+++++---+
| BT-1034.06 | Status of Meteorological Sounding Balloons | Robert
Leviton | 1963-12-01 |
http://library.gsfc.nasa.gov/Databases/Balloon/Data/BT1034.06.pdf |
+++++---+
1 row in set (0.00 sec)
mysql SELECT * FROM balloon_rec WHERE access_no=(SELECT access_no FROM
balloon_txt WHERE MATCH (access_no,recs_txt) AGAINST ('robin'));
ERROR 1242 (21000): Subquery returns more than 1 row
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery SQL help
I could not get the subquery to work, but the sql below did what I
wanted:^)
SELECT balloon_rec.* FROM `balloon_rec` LEFT JOIN `balloon_txt` USING
(`access_no`) WHERE MATCH
(`balloon_txt`.`access_no`,`balloon_txt`.`recs_txt`) AGAINST
('meteorology')
On Sat, 17 Jul 2004 12:15:20 -0400, leegold [EMAIL PROTECTED]
said:
I tried what's below, seemed OK, so I replaced an IN for the = in
the subquery below because of the subquery's error message. I thought
w/IN I'd get three (3) records returned as expected. But when I tried
IN I got my entire DB returned - I don't show that below - it kept
scrolling so I aborted it. What did I do wrong? Seems like the inner
just returns three recs. and should match-up w/the outer for three recs.
too. I'm using 4.1. Thanks, any help arrpeciated. Lee G.
mysql SELECT access_no FROM balloon_txt WHERE MATCH
(access_no,recs_txt) AGAINST ('robin');
++
| access_no |
++
| BT-1037.11 |
| BT-2540|
| BT-1034.06 |
++
3 rows in set (0.01 sec)
mysql SELECT * FROM balloon_rec WHERE access_no='BT-1034.06';
+++++---+
| access_no | title | author
| doc_date | elec_access
|
+++++---+
| BT-1034.06 | Status of Meteorological Sounding Balloons | Robert
Leviton | 1963-12-01 |
http://library.gsfc.nasa.gov/Databases/Balloon/Data/BT1034.06.pdf |
+++++---+
1 row in set (0.00 sec)
mysql SELECT * FROM balloon_rec WHERE access_no=(SELECT access_no FROM
balloon_txt WHERE MATCH (access_no,recs_txt) AGAINST ('robin'));
ERROR 1242 (21000): Subquery returns more than 1 row
--
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]
SQL help
What I really want was mysql update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null); but that gives a syntax error and I don't think I can do a select inside an update. :-( mysql update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null; ERROR 1109: Unknown table 'audit_log' in where clause -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
Bob Lockie wrote: What I really want was mysql update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null); but that gives a syntax error and I don't think I can do a select inside an update. :-( Subqueries require mysql 4.1 or higher. mysql update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null; ERROR 1109: Unknown table 'audit_log' in where clause You have to name all the tables you need in the UPDATE clause before you can use them in the WHERE clause. So, you need UPDATE records, auditlog, audit_log_records SET records.prio=2 WHERE audit_log.tracker_id=audit_log_records.tracker_id AND records.id=audit_log_records.id AND audit_log.operation='D' AND audit_log.completed is null; This is a multiple-table update, which is supported starting with mysql 4.0.4. Prior to that, you can't do this with one statement. See the manual http://dev.mysql.com/doc/mysql/en/UPDATE.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
Bob Lockie [EMAIL PROTECTED] wrote: What I really want was mysql update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null); but that gives a syntax error and I don't think I can do a select inside an update. :-( mysql update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null; ERROR 1109: Unknown table 'audit_log' in where clause You must specify 'audit_log' and 'audit_log_records' tables too. UPDATE records, audit_log, audit_log_records SET records.prio=2 WHERE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql SQL Help
Hi, Need a SQL/Design help. I have a table as follows: ORDER_HISTORY -- ORDER_ID | ORDER_NAME | ORDER_AMOUNT | ORDER_DATE -- 10 | Order 1| 20.00| 10-Jan-2003 11 | Order 2| 200.00 | 15-Jan-2003 12 | Order 3| 250.00 | 25-Jan-2003 13 | Order 4| 260.00 | 30-Mar-2003 14 | Order 5| 320.00 | 10-Jan-2004 I want to write a SQL which generates a weekly report from the ORDER_HISTORY table and shows it as: WEEKLY_DATE | CUMULATIVE AMOUNT -- 10-JAN-2003 | 20 17-JAN-2003 | 220 24-JAN-2003 | 220 31-JAN-2003 | 490 07-FEB-2003 | 490 Similarly another table without a Cumulative: WEEKLY_DATE | AMOUNT -- 10-JAN-2003 | 20 17-JAN-2003 | 200 24-JAN-2003 | 0 31-JAN-2003 | 250 07-FEB-2003 | 0 My thought process currently is to first get all the date intervals within the min and max dates of ORDER_DATE with a 1 WEEK addition to each min date and then for each of these intervals, I can then get the AMOUNT and add it to up to the previous amount if cumulative or just leave it if not cumulative. I am trying to find an elegant solution to implement this and not have too many trips to the database, i.e once to get each interval of date between the min and max and then for each interval go to the db again to get the amount. Is there any function which will return all the starting dates of each week between two dates? Appreciate any help or links to prior solutions or any book on this topic. thanks ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql SQL Help
I can't suggest a complete solution but I think the answer is going to start with the week() function. See http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1403. You're lucky; figuring out which week a date belongs to was something I had to do in DB2 a few years back. The user was on an old version of DB2 that didn't have the week() function yet and I had to do a *lot* of work to come up with a way to calculate weeks without it. That solution involved writing scripts to automatically build tables showing the week start and end dates so that he could join to them just to get week numbers. In your case, you can probably get what you need by simply selecting all the rows you want to report on, then adding a GROUP BY for the week number. That should work for the second of your reports, the one that shows actual totals for the week in question. I'm not so sure how to do cumulative totals in MySQL. It's easy enough in a spreadsheet so you should be able to think of some way to do it in MySQL. Like I said, I don't know the whole answer but I think you definitely want to start with the week() function in a GROUP BY. Be sure you look at the different modes supported by the week() function and make sure you pick the one that matches the rules in your environment. Rhino - Original Message - From: Ramesh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 01, 2004 11:12 PM Subject: MySql SQL Help Hi, Need a SQL/Design help. I have a table as follows: ORDER_HISTORY -- ORDER_ID | ORDER_NAME | ORDER_AMOUNT | ORDER_DATE -- 10 | Order 1| 20.00| 10-Jan-2003 11 | Order 2| 200.00 | 15-Jan-2003 12 | Order 3| 250.00 | 25-Jan-2003 13 | Order 4| 260.00 | 30-Mar-2003 14 | Order 5| 320.00 | 10-Jan-2004 I want to write a SQL which generates a weekly report from the ORDER_HISTORY table and shows it as: WEEKLY_DATE | CUMULATIVE AMOUNT -- 10-JAN-2003 | 20 17-JAN-2003 | 220 24-JAN-2003 | 220 31-JAN-2003 | 490 07-FEB-2003 | 490 Similarly another table without a Cumulative: WEEKLY_DATE | AMOUNT -- 10-JAN-2003 | 20 17-JAN-2003 | 200 24-JAN-2003 | 0 31-JAN-2003 | 250 07-FEB-2003 | 0 My thought process currently is to first get all the date intervals within the min and max dates of ORDER_DATE with a 1 WEEK addition to each min date and then for each of these intervals, I can then get the AMOUNT and add it to up to the previous amount if cumulative or just leave it if not cumulative. I am trying to find an elegant solution to implement this and not have too many trips to the database, i.e once to get each interval of date between the min and max and then for each interval go to the db again to get the amount. Is there any function which will return all the starting dates of each week between two dates? Appreciate any help or links to prior solutions or any book on this topic. thanks ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- 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]
SQL-help needed
Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58.
RE: SQL-help needed
I'm still a beginner myself but try something like SELECT COUNT(YEAR), WINNER1 AS WINNER, WINNER2 AS WINNER, YEAR FROM CHAMPIONS WHERE CLASS = hd GROUP BY WINNER; I think this will give you something like: COUNT(YEAR) WINNER YEAR 2 carl1957 2 carl1985 1 mattias 1957 1 erik1985 Again I am a beginner and would have to test this to see if it actually gives me the right info or if I would have to tweek it. Respectfully, Ligaya Turmelle Computer Programmer Guam International Country Club 495 Battulo Street Dededo, Guam 96912 Tel: (671) 632-4445 Fax: (671) 632-4440 Reservations: (671) 632-4422 (GICC) -Original Message- From: Carl Schéle, IT, Posten [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 6:50 PM To: [EMAIL PROTECTED] Subject: SQL-help needed Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL-help needed
Carl Schéle, IT, Posten wrote: Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. The following is close to what you want: CREATE TEMPORARY TABLE champs (winner CHAR(10), year INT); # change the column types to match table champions INSERT INTO champs SELECT winner_1, year FROM champions WHERE class='hd'; INSERT INTO champs SELECT winner_2, year FROM champions WHERE class='hd' AND winner_2 IS NOT NULL; # some of your winner_2 spots are empty. If they're empty strings # instead of NULL, change IS NOT NULL to != '' SELECT * FROM champs ORDER by winner, year; +-+--+ | winner | year | +-+--+ | carl| 1957 | | carl| 1985 | | erik| 1985 | | mattias | 1957 | +-+--+ 4 rows in set (0.01 sec) SELECT count(*), winner FROM champs GROUP BY winner; +--+-+ | count(*) | winner | +--+-+ |2 | carl| |1 | erik| |1 | mattias | +--+-+ 3 rows in set (0.01 sec) === It seems to me that your table design is what makes this difficult. If you changed it to something like the following, where wintype denotes winner_1 or winner_2, this would be easier: id class winner wintype year - 1 hd carl 1 1957 2 hs daniel 1 1982 3 hd erik 1 1985 4 js erik 1 1974 5 hd mattias2 1957 6 hd carl 2 1985 You could then go straight to the select: SELECT * FROM champions WHERE class='hd' ORDER by winner, year; or SELECT count(*), winner FROM champions WHERE class='hd' GROUP BY winner; You could use a variant of the INSERT-SELECTs above to fill the new table, if you decide to go that way. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL-HELP
Hi! I got a table, champions, looking like this: id class winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. Here's some help for starters: SELECT DISTINCT CASE WHEN c1.winner_1=c1.winner_1 THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1,champions c2 /Carl
SQL-HELP
Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl
Re: SQL-HELP
Hello, you might want to try select DISTINCT ? http://www.mysqlfreaks.com/statements/18.php /Jonas - Original Message - From: Carl Schéle, IT, Posten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 3:49 PM Subject: SQL-HELP Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: SQL-HELP
yeah but that wont really do it since the names are in two columns so, there must also be a UNION included do a union and then select distinct on the result from the union that should do it (eller hur?) -Ursprungligt meddelande- Från: Jonas Lindén [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:01 Till: Carl Schéle; IT; Posten; [EMAIL PROTECTED] Ämne: Re: SQL-HELP Hello, you might want to try select DISTINCT ? http://www.mysqlfreaks.com/statements/18.php /Jonas - Original Message - From: Carl Schéle, IT, Posten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 3:49 PM Subject: SQL-HELP Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl -- 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]
SV: SQL-HELP
Now UNION is implemented in MySQL 4.0.0. and as I stated earlier I run 3.23.58. -Ursprungligt meddelande- Från: Rodolphe Toots [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:19 Till: Jonas Lindén; Carl Schéle; [EMAIL PROTECTED] Ämne: SV: SQL-HELP yeah but that wont really do it since the names are in two columns so, there must also be a UNION included do a union and then select distinct on the result from the union that should do it (eller hur?) -Ursprungligt meddelande- Från: Jonas Lindén [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:01 Till: Carl Schéle; IT; Posten; [EMAIL PROTECTED] Ämne: Re: SQL-HELP Hello, you might want to try select DISTINCT ? http://www.mysqlfreaks.com/statements/18.php /Jonas - Original Message - From: Carl Schéle, IT, Posten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 3:49 PM Subject: SQL-HELP Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL-HELP
Carl Schéle, IT, Posten wrote: I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). The smart way: get a database that understands UNION. The other way: SELECT DISTINCT CASE WHEN c1.id = c1.id THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1, champions c2 Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: SQL-HELP
It works fine (with a little tweak). SELECT DISTINCT CASE WHEN c1.winner_1 = c1.winner_2 THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1,champions c2 ORDER BY winner ASC is what I wanted. Thank you very much! Btw, I can't help my webhotel is rotten and only uses old versions. But it's cheap :) /Carl -Ursprungligt meddelande- Från: Jochem van Dieten [mailto:[EMAIL PROTECTED] Skickat: den 17 februari 2004 16:44 Till: Carl Schéle, IT, Posten Kopia: [EMAIL PROTECTED] Ämne: Re: SQL-HELP Carl Schéle, IT, Posten wrote: I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). The smart way: get a database that understands UNION. The other way: SELECT DISTINCT CASE WHEN c1.id = c1.id THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1, champions c2 Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Help
Hello, Hopefully you sql guru's can help me out a bit. :) Here is the short example of what I want to accomplish. I wish to have the difference between two different select queries. So if one query pulls records 1,2,3 and 4 and the second pulls records 1 and 4 I wish to have only the records 2 and 3. How can I accomplish this easily. In case that doesn't make sense here is the long version. I have two tables that are keyed together through an id field. However this is not a one to one relationship, it is a one to many relationship. The following is an example of the table Table 1 Table 2 -- ID relid rid vid Table 1 and table two are linked through the columns id and rid. There can be many links between id and rid so the link is further refined through a vid field. What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Which means that I want to find the parents who don't have children 46. A great way to do this would be with a minus operator but that isn't supported. Or to add two queries to a temporary table then delete the duplicate records but I don't know how to do that either. I hope this is clear I know it's confusing. But it's really slowing me down. Joe
Re: SQL Help
* sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, In regards to my last e-mail what would be great is if I can get all the records in tab1 then subtract from there all the records that match the query tab1.id=tab2.rid and tab2.vid=46. The result would give me what I need but alas mysql doesn't support minus. Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Gerald, Your right. You and Roger hit it on the head. Stupid me miss read Roger's original post. Last night I was banging my head on the left and right joins but I didn't understand it until I read Gerald's last note. Plus I didn't realize you can put two conditions in the ON clause which is why I didn't get Roger's post. Thank you very much guys. You saved the day. Joe On Friday, January 16, 2004, at 12:31 PM, gerald_clark wrote: That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- 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]
SQL Help please
SQL guru's, I could use some help writing a bit of SQL. There's 3 tables: orderable_parts partID varchar, topCatID int, # top level category ID ... part_attributes partID varchar, attName varchar, attValue varchar, ... topcatattributevalues tcavID int, topCatID int, attName varchar, attValue varchar, ... orderable_parts has parts available on our web site. Theres about 40,000 of them part_attributes are related to parts. Color, size etc. ~150,000 rows topcatattributevalues is a list of all the distinct part attributes available in each top level category. They're used for web searches and for product managers to edit orderable_parts, ~100,000 rows I'm trying to build a function to delete records from topcatattributevalues that are not used in any orderable_part. Either 1 delete statement or an update status=-1 and a delete where status=-1 would work. But it looks to me like I need a 3 way outer join and I can't seem to get it to work. I tried a variety of statements that look something like: update topcatattributevalues t left outer join orderable_parts o on (t.topCatID=o.topCatID), left outer join part_attributes p on (t.attName=p.attName and t.attValue=p.attValue and o.partID=p.partID) set t.status=-1 where o.partID is null I can't seem to get it. Any assistance would be greatly appreciated. Thanks. Dave. _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help...
Estoy tomando el sol . q -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help...
Estoy tomando el sol . q -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Help...
After some searching around different books/manuals/google I still can't seem to figure out how do to this. What I have is a table with 4 cols (task, resource, department, priority) and what I want to do is be able to select distinct resources and list what their highest priority is. In other words, if a resource is in a high priority task and a low priority task, I only want to show the high priority task. Thanks for the help! -Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --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]
Re: SQL Help...
At 02:52 PM 7/24/2003, you wrote: After some searching around different books/manuals/google I still can't seem to figure out how do to this. What I have is a table with 4 cols (task, resource, department, priority) and what I want to do is be able to select distinct resources and list what their highest priority is. In other words, if a resource is in a high priority task and a low priority task, I only want to show the high priority task. Thanks for the help! -Nick Nick, You mean something like this: select resource, Max(Concat(priority, '=', Resource)) ResourcePriority group by Resource This will work with priority 1 through 9. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
Try: select task, resource, department, max(priority) from table group by task, resource, department Regards, Ulises -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: Jueves 24 de Julio de 2003 02:53 PM Para: [EMAIL PROTECTED] Asunto: SQL Help... After some searching around different books/manuals/google I still can't seem to figure out how do to this. What I have is a table with 4 cols (task, resource, department, priority) and what I want to do is be able to select distinct resources and list what their highest priority is. In other words, if a resource is in a high priority task and a low priority task, I only want to show the high priority task. Thanks for the help! -Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help...
That works great. =D Knew it shouldn't be that difficult, thanks a bunch. And it actually works with the Priorities being in text form to (low, med, hi). -Nick At 02:52 PM 7/24/2003, you wrote: After some searching around different books/manuals/google I still can't seem to figure out how do to this. What I have is a table with 4 cols (task, resource, department, priority) and what I want to do is be able to select distinct resources and list what their highest priority is. In other words, if a resource is in a high priority task and a low priority task, I only want to show the high priority task. Thanks for the help! -Nick Nick, You mean something like this: select resource, Max(Concat(priority, '=', Resource)) ResourcePriority group by Resource This will work with priority 1 through 9. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
Nick, As you stated, your priority field datatype is varchar, with possible values Hi, Medium and Low, as opposed to being integers. The use of max function, as suggested by some colleagues without knowing exactly the datatype would work correctly only on columns of datatype integer. In your case, for textual columns, lexicographic (dictionary) ordering will be used in computing function max, and Medium would win the competition, instead of Hi -- which actually has the lowest ranking in the lexicographic ordering. Perhaps you could use the CASE WHEN ... constructs to map your textual priority into numeric (integer) values (e.g., L - 1, M - 2, H - 3), and then apply the max function to the integer values to get correct results. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 4:41 PM To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: SQL Help... I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
Yep, saw that and did just as you stated :) Nick, As you stated, your priority field datatype is varchar, with possible values Hi, Medium and Low, as opposed to being integers. The use of max function, as suggested by some colleagues without knowing exactly the datatype would work correctly only on columns of datatype integer. In your case, for textual columns, lexicographic (dictionary) ordering will be used in computing function max, and Medium would win the competition, instead of Hi -- which actually has the lowest ranking in the lexicographic ordering. Perhaps you could use the CASE WHEN ... constructs to map your textual priority into numeric (integer) values (e.g., L - 1, M - 2, H - 3), and then apply the max function to the integer values to get correct results. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 4:41 PM To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: SQL Help... I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --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] -- 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]
sql help
hi, wonder if the sql gurus can help with this one. i have two tables (simplified), tblAccounts and tblAccountsServices. tblAccounts has an ID (PK) and an accountNumber, and tblAccountsServices has an ID (PK) and accountID (FK to tblAccounts.ID). i want to run a query that gives me a row for each record in tblAccountsServices that looks like - tblAccountsServices.ID, tblAccounts.accountNumber, COUNT of tblAccountsServices.ID for this tblAccountsServices.accountID i'm thinking something like - select acctSrv.ID, a.accountNumber, select COUNT( ID) from tblAccountsServices where accountID = ?? as theCount from tblAccountsServices acctSrv, tblAccounts a where a.ID = acctSrv.accountID it's the part in quotes i'm having trouble with, if it's even possible. i feel like i've seen something similar posted before. of course, if i have to, i can use the brute force method of looping through the rowset of tblAccountsServices, and if it's a new accountID from the previous record, then run another query to get the count then... btw, i'm running mysql 3.23.53-max-nt... thanks! - 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: sql help
You want to look at 'group by acctSrv.accountID' rather than a compound select. On Mon, 23 Dec 2002, Adam Nowalsky wrote: Date: Mon, 23 Dec 2002 09:04:32 -0500 From: Adam Nowalsky [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: sql help hi, wonder if the sql gurus can help with this one. i have two tables (simplified), tblAccounts and tblAccountsServices. tblAccounts has an ID (PK) and an accountNumber, and tblAccountsServices has an ID (PK) and accountID (FK to tblAccounts.ID). i want to run a query that gives me a row for each record in tblAccountsServices that looks like - tblAccountsServices.ID, tblAccounts.accountNumber, COUNT of tblAccountsServices.ID for this tblAccountsServices.accountID i'm thinking something like - select acctSrv.ID, a.accountNumber, select COUNT( ID) from tblAccountsServices where accountID = ?? as theCount from tblAccountsServices acctSrv, tblAccounts a where a.ID = acctSrv.accountID it's the part in quotes i'm having trouble with, if it's even possible. i feel like i've seen something similar posted before. of course, if i have to, i can use the brute force method of looping through the rowset of tblAccountsServices, and if it's a new accountID from the previous record, then run another query to get the count then... btw, i'm running mysql 3.23.53-max-nt... thanks! - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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
sql help
hi, wonder if the sql gurus can help with this one. i have two tables (simplified), tblAccounts and tblAccountsServices. tblAccounts has an ID (PK) and an accountNumber, and tblAccountsServices has an ID (PK) and accountID (FK to tblAccounts.ID). i want to run a query that gives me a row for each record in tblAccountsServices that looks like - tblAccountsServices.ID, tblAccounts.accountNumber, COUNT of tblAccountsServices.ID for this tblAccountsServices.accountID i'm thinking something like - select acctSrv.ID, a.accountNumber, select COUNT( ID) from tblAccountsServices where accountID = ?? as theCount from tblAccountsServices acctSrv, tblAccounts a where a.ID = acctSrv.accountID it's the part in quotes i'm having trouble with, if it's even possible. i feel like i've seen something similar posted before. of course, if i have to, i can use the brute force method of looping through the rowset of tblAccountsServices, and if it's a new accountID from the previous record, then run another query to get the count then... thanks! - 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
sql help
btw, regarding my last post, i'm running mysql 3.23.53-max-nt... - 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: AW: SQL help, search with related record in 2nd table
Carsten Thank you very much for pointing me in that direction, that nearly solved the issue but I still get a duplicate for each record that is in the second table (if WishListItems has 5 records for a Visitor, I get 5 duplicates in the result). I tried using GROUP BY to eliminate the duplicates and this seems to work, but it seems to too much overhead, or is it? Is writing the query this way correct? SELECT * FROM Visitors LEFT JOIN WishListItems ON Visitors.VisitorID=WishListItems.VisitorID WHERE WishListItems.VisitorID IS NOT NULL AND Visitors.NameLast='Collins' GROUP BY Visitors.NameLast; Michael At 9:33 AM +0200 9/11/02, Carsten Zilch wrote: Hello, the answer for: RE: Selecting data from one table if it's NOT in another should also answer your question Carsten -Ursprüngliche Nachricht- Von: Michael Collins [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 10. September 2002 22:40 An: [EMAIL PROTECTED] Betreff: SQL help, search with related record in 2nd table I am looking for some help on a SQL query to perform a search, but constrain the search to those records which have a related record in a second table. What I think I need is a subselect, but since this is not possible in 3.23.47, I am looking for work around. I have one table for Visitors (key is VisitorID) and another for items that a user has selected named WishListItems (foreign key is VisitorID). SELECT Visitors.* FROM Visitors, WishListItems WHERE Visitors.VisitorID=WishListItems.VisitorID AND Visitors.NameLast REGEXP 'Collins'; I get a result for each record there is in WishListItems. I tried using a Join but that does not help: SELECT Visitors.* FROM Visitors INNER JOIN WishListItems On Visitors.VisitorID=WishListItems.VisitorID WHERE Visitors.NameLast REGEXP 'Collins'; I seem to get the same result. What I need is to search Visitors, but only find records from Visitors if there are any records in WishListItems (I am not checking anythign in WishListItems, there just has to be at least one record). -- Michael __ -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net - 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
SQL help, search with related record in 2nd table
I am looking for some help on a SQL query to perform a search, but constrain the search to those records which have a related record in a second table. What I think I need is a subselect, but since this is not possible in 3.23.47, I am looking for work around. I have one table for Visitors (key is VisitorID) and another for items that a user has selected named WishListItems (foreign key is VisitorID). SELECT Visitors.* FROM Visitors, WishListItems WHERE Visitors.VisitorID=WishListItems.VisitorID AND Visitors.NameLast REGEXP 'Collins'; I get a result for each record there is in WishListItems. I tried using a Join but that does not help: SELECT Visitors.* FROM Visitors INNER JOIN WishListItems On Visitors.VisitorID=WishListItems.VisitorID WHERE Visitors.NameLast REGEXP 'Collins'; I seem to get the same result. What I need is to search Visitors, but only find records from Visitors if there are any records in WishListItems (I am not checking anythign in WishListItems, there just has to be at least one record). -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net - 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
SQL Help Needed
I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. Please help! - 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: SQL Help Needed
Ok this should be easy so I'm prolly going to screw it up, but here goes =D Your query should be: SELECT UserID, SUM(points) FROM History WHERE WeekID = 'whatever' GROUP BY UserIdI think that should do it. Someone yell if its wrong though. -Nick I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. Please help! - 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: SQL Help Needed
* Dave I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. I don't know what you have tried or why it did not work for you, but something like this should work: SELECT u.UserID, IF(h.Points,h.Points,0) AS Points FROM Users AS u LEFT JOIN History AS h USING(UserID) WHERE ISNULL(WeekID) OR WeekID = 1 ORDER BY u.UserID -- Roger - 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: SQL Help Needed
Thanks for your responses but it's not that much help I need with my SQL ;-) None of the 3 suggestions work. Please look at the examples I gave. I need *all* UserIDs regardless of whether they have a record in History that matches both UserID and WeekID. In other words some UserIDs will only exist for WeekID = 2, other for 1 and 2 and so on. Cheers Dave - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Dave [EMAIL PROTECTED] Sent: Thursday, May 23, 2002 6:21 PM Subject: RE: SQL Help Needed * Dave I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. I don't know what you have tried or why it did not work for you, but something like this should work: SELECT u.UserID, IF(h.Points,h.Points,0) AS Points FROM Users AS u LEFT JOIN History AS h USING(UserID) WHERE ISNULL(WeekID) OR WeekID = 1 ORDER BY u.UserID -- Roger - 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: SQL Help Needed
* Dave Thanks for your responses but it's not that much help I need with my SQL ;-) None of the 3 suggestions work. Please look at the examples I gave. I need *all* UserIDs regardless of whether they have a record in History that matches both UserID and WeekID. ...and that is what I thought my suggestion would do... SELECT u.UserID, IF(h.Points,h.Points,0) AS Points FROM Users AS u LEFT JOIN History AS h USING(UserID) WHERE ISNULL(WeekID) OR WeekID = 1 ORDER BY u.UserID Maybe you could tell us what error message you got, or in what way the query 'does not work'? -- Roger - 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
SQL HELP!
What the hell this query do!? SELECT student.name FROM student WHERE not exists( SELECT * FROM enrollment WHERE not exists( SELECT * FROM class WHERE class.name = enrollment.classname AND enrollment.studentnumber = student.sid ) ) It returns the name of the students which are enrolled in a class? I need to know what returns in order to translate it into SQL that mysql can parse... Any help would be appreciated! Roberto Ramírez - 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
sql help examining log table
Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - 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: sql help examining log table
I might be wrong, but this may get you going in the right direction: select count(subject) as count, subject from outgoing where auth='USER' group by subject order by timestamp desc limit 50 This would tell you each subject ever sent by the user, and how many times that subject has been sent. Such as: +---+--+ | count | subject | +---+--+ | 12| Subject 1| | 36| Subject 2| | 2| Another subject | +---+--+ And you can narrow it down if you know the subject such as: select count(subject) as count, subject from outgoing where auth='USER' and subject = 'Subject 2' group by subject order by timestamp desc limit 50 This would return +---+--+ | count | subject | +---+--+ | 36| Subject 2| +---+--+ I think this is right, if not hopefully my logic is. Regardless, hopefully something can be useful. Dan -Original Message- From: Viraj Alankar [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:35 PM To: [EMAIL PROTECTED] Subject: sql help examining log table Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - 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: sql help examining log table
Ditch the Limit 50 in those queries I wrote, they won't work, they only limit the result set, not the searched set. I don't know what I was thinking. If you need to searched set to be limited by the last 50 of the user, I cannot think of anything at this time, perhaps after a few more cups of coffee:) -Original Message- From: Dan Vande More [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:44 PM To: [EMAIL PROTECTED] Subject: RE: sql help examining log table I might be wrong, but this may get you going in the right direction: select count(subject) as count, subject from outgoing where auth='USER' group by subject order by timestamp desc limit 50 This would tell you each subject ever sent by the user, and how many times that subject has been sent. Such as: +---+--+ | count | subject | +---+--+ | 12| Subject 1| | 36| Subject 2| | 2| Another subject | +---+--+ And you can narrow it down if you know the subject such as: select count(subject) as count, subject from outgoing where auth='USER' and subject = 'Subject 2' group by subject order by timestamp desc limit 50 This would return +---+--+ | count | subject | +---+--+ | 36| Subject 2| +---+--+ I think this is right, if not hopefully my logic is. Regardless, hopefully something can be useful. Dan -Original Message- From: Viraj Alankar [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:35 PM To: [EMAIL PROTECTED] Subject: sql help examining log table Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - 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 - 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: sql help examining log table
Hi Viraj, You can do it using temporary table. Create temporary table tmp select subject from outgoing where auth='USER' order by timestamp desc limit 50; Select count(distinct subject) from tmp group by subject; drop table tmp; If the result of the second query is 1 all the last 50 messages have the same subject. It is assumed there are at least 50 rows for auth = 'USER'. Regards Anvar. At 03:34 PM 17/03/2002 -0500, you wrote: Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - 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
SQL Help, Please...
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as b,
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at the same
value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid and bid,
but it does not return the bidderId correctly.
Can anybody help?
Cheers
Carl
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23 21:42:50',
'2002-04-30 11:30:00', 'active');
INSERT INTO sa_auction (id, start, end, state) VALUES (2, '2002-01-23 21:42:50',
'2002-02-09 06:30:00', 'expired');
INSERT INTO sa_auction (id, start, end, state) VALUES (3, '2002-03-23 21:42:50',
'2002-07-05 09:00:00', 'waiting');
INSERT INTO sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting');
#
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL auto_increment,
auctionId int(11) NOT NULL default '0',
cellId tinyint(4) NOT NULL default '0',
bid int(11) NOT NULL default '0',
bidderId mediumint(9) NOT NULL default '0',
bidtime timestamp(14) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_bid`
#
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (1, 1, 5, 1,
1, 20020128225421);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (2, 1, 5, 2,
2, 20020128225424);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
15, 2, 20020213214856);
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (4, 1, 5, 3,
4, 20020213215649);
This e-mail and any attachments are confidential. If you are not the intended
recipient, please notify us immediately by reply e-mail and then delete this message
from your system. Do not copy this e-mail or any attachments, use the contents for any
purpose, or disclose the contents to any other person: to do so could be a breach of
confidence.
-
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: SQL Help, Please...
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at
the same value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid
and bid, but it does not return the bidderId correctly.
Can anybody help?
Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.
Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.
In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
Cheers
Carl
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
'2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting'); #
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL auto_increment,
auctionId int(11) NOT NULL default '0',
cellId tinyint(4) NOT NULL default '0',
bid int(11) NOT NULL default '0',
bidderId mediumint(9) NOT NULL default '0',
bidtime timestamp(14) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_bid`
#
INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES
(1, 1, 5, 1, 1, 20020128225421); INSERT INTO sa_bid (id, auctionId, cellId,
bid, bidderId, bidtime) VALUES (2, 1, 5, 2, 2, 20020128225424); INSERT INTO
sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
15, 2, 20020213214856); INSERT INTO sa_bid (id, auctionId, cellId, bid,
bidderId, bidtime) VALUES (4, 1, 5, 3, 4, 20020213215649);
This e-mail and any attachments are confidential. If you are not the
intended recipient, please notify us immediately by reply e-mail and then
delete this message from your system. Do not copy this e-mail or any
attachments, use the contents for any purpose, or disclose the contents to
any other person: to do so could be a breach of confidence.
-
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,
Re: SQL Help, Please...
Hi,
At 11:21 AM 14/02/2002 -0500, you wrote:
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
Hi
I am trying to write an auction script that uses mysql as its backend. Each
auction can have upto 25 sub auctions(cells) taking place.
I'm trying to query the DB to give me a list of all the successfull bids
for each cell, for each auction...
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ORDER BY bidTime DESC
This is further complicated in so much that multiple MAX bids may exist at
the same value, but, only the earliest should be returned for each cell.
Which is returning some of the columns correctly, namely auctionid, cellid
and bid, but it does not return the bidderId correctly.
Can anybody help?
Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.
This behaviour is well documented in the manual. There is a very practical
reason to allow this behaviour. Suppose for efficiency reasons data is
denormalized
and for example, id,name and some other particulars are all kept in the same
table . If the database was very strict that all the columns selected
should be
in the group by expression, one will have to put all these columns (id,name,..)
in the group by clause. Then the db engine will have to take all these
fields in
the intermediate ordering phase of the query execution. Surely this will be
inefficient in both time and space. But with the 'incorrect' behaviour of
Mysql
one can put all the data columns required to be returned in the select and do
group by only the id. This would make the query to complete very fast compared
to the former and the effect will be even more pronounced with index on id
field.
Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.
In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
Cheers
Carl
The problem can be solved by using temporary tables.
Create temporary table tmp1
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid FROM sa_bid as b,
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ;
Create temporary table tmp2
Select t1.auctionid, t1.cellid, t1.bid, min(b.bidtime) as bidtime from tmp1
as t1, sa_bid as b
where (t1.auctionid = b.auctionid and t1.cellid = b.cellid and t1.bid =
b.bid)
group by t1.auctionid,t1.cellid,t1.bid
Select t2.*, b.bidderid from tmp2 as t2, sa_bid as b
where t2.auctionid = b.auctionid and t2.cellid = b.cellid and t2.bid =
b.bid and t2.bidtime = b.bidtime
I hope there may be better and simpler ways to achieve the objective.
Surely correlated subquery and derived table features might have been good
features for such
situations.
Anvar.
#
# Table structure for table `sa_auction`
#
CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `sa_auction`
#
INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
'2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
'2002-08-01 11:30:00', 'waiting'); #
#
# Table structure for table `sa_bid`
#
CREATE TABLE sa_bid (
id int(11) NOT NULL
SQL help plz
Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - 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: SQL help plz
At 21:36 +0200 1/30/02, P.Agenbag wrote: Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. Create another table to hold the maximum date associated with each key value (which I'm calling k rather than key because key is a reserved word): CREATE TABLE t2 SELECT k, MAX(date) AS date FROM t GROUP BY k; Then join this table with the original to get the rows with the appropriate k and date values, printing out the name and date values: SELECT t.name, t.date FROM t, t2 WHERE t.k = t2.k and t.date = t2.date ORDER BY name; - 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: SQL help plz
mysql describe mytable; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | YES | | NULL| | | keyq | int(11) | YES | | NULL| | | name | char(20) | YES | | NULL| | | dateq | date | YES | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) select name,max(dateq) from mytable group by name; -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:36 PM To: mysql Subject: SQL help plz Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - 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: SQL help plz
Sorry, I meant: mysql select name,max(dateq) from mytable group by keyq; -Original Message- From: Rick Emery Sent: Wednesday, January 30, 2002 2:14 PM To: 'P.Agenbag'; mysql Subject: RE: SQL help plz mysql describe mytable; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | YES | | NULL| | | keyq | int(11) | YES | | NULL| | | name | char(20) | YES | | NULL| | | dateq | date | YES | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) select name,max(dateq) from mytable group by name; -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:36 PM To: mysql Subject: SQL help plz Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - 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: SQL help plz
Further, I'd advise NOT using field names like date and key. Using eserved words is never a good prcatice. -Original Message- From: Rick Emery Sent: Wednesday, January 30, 2002 2:16 PM To: 'P.Agenbag'; 'mysql' Subject: RE: SQL help plz Sorry, I meant: mysql select name,max(dateq) from mytable group by keyq; -Original Message- From: Rick Emery Sent: Wednesday, January 30, 2002 2:14 PM To: 'P.Agenbag'; mysql Subject: RE: SQL help plz mysql describe mytable; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | YES | | NULL| | | keyq | int(11) | YES | | NULL| | | name | char(20) | YES | | NULL| | | dateq | date | YES | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) select name,max(dateq) from mytable group by name; -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:36 PM To: mysql Subject: SQL help plz Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - 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: SQL help plz
Try this: select key_col, min(name), max(date_col) from my_table group by key_col ; You could use max(name) instead of min(name) also, although since the names can be misspelled, I don't see why it would matter which name is displayed. s.s. On Wed, 30 Jan 2002 21:36:04 +0200, you wrote: Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - 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
SQL help
How do I write SQL in MySQL to randomly select 5 records from a table? - 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: SQL help
SELECT * FROM db ORDER BY rand() LIMIT 5 --zak - Original Message - From: Webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 08, 2001 9:38 PM Subject: SQL help How do I write SQL in MySQL to randomly select 5 records from a table? - 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: SQL help
i think it is more suitable to do it in programming level in mysql, though I know there is a function is M$sql server. if your table schema has a unique id field, before submit a query to DB, 1) select count(*) from it 2) from the above resultset, program to draw whatever number of records you want. 3) submit the query, select * from it where id in (your random record ids); - Original Message - From: Webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 11:38 AM Subject: SQL help How do I write SQL in MySQL to randomly select 5 records from a table? - 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: SQL help
i dont follow step #3. At 11:42 PM 5/8/2001, Tony Shiu wrote: i think it is more suitable to do it in programming level in mysql, though I know there is a function is M$sql server. if your table schema has a unique id field, before submit a query to DB, 1) select count(*) from it 2) from the above resultset, program to draw whatever number of records you want. 3) submit the query, select * from it where id in (your random record ids); - Original Message - From: Webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 11:38 AM Subject: SQL help How do I write SQL in MySQL to randomly select 5 records from a table? - 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 - 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: SQL help
for 3) if you have a set of random values e.g. 1,3, 6, 9, 4 then your sql will be select * from your-table where id in (1, 3, 6, 9, 4); FYI: if you are using ver 3.23.X, you may use SELECT * FROM your-table ORDER BY rand() LIMIT 5 contributed from: Zak Greant [EMAIL PROTECTED] rand() seems using primary key of your table. mine works in both old and new versions. - Original Message - From: Webmaster [EMAIL PROTECTED] To: Tony Shiu [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 12:27 PM Subject: Re: SQL help i dont follow step #3. At 11:42 PM 5/8/2001, Tony Shiu wrote: i think it is more suitable to do it in programming level in mysql, though I know there is a function is M$sql server. if your table schema has a unique id field, before submit a query to DB, 1) select count(*) from it 2) from the above resultset, program to draw whatever number of records you want. 3) submit the query, select * from it where id in (your random record ids); - Original Message - From: Webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 11:38 AM Subject: SQL help How do I write SQL in MySQL to randomly select 5 records from a table? - 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 - 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: SQL help
You might be able to do something like this instead: SELECT *, id*0+RAND() AS r FROM table ORDER BY r LIMIT 5; Check the list archives, this issue comes up quite often. On Wed, 9 May 2001, Webmaster wrote: i dont follow step #3. At 11:42 PM 5/8/2001, Tony Shiu wrote: i think it is more suitable to do it in programming level in mysql, though I know there is a function is M$sql server. if your table schema has a unique id field, before submit a query to DB, 1) select count(*) from it 2) from the above resultset, program to draw whatever number of records you want. 3) submit the query, select * from it where id in (your random record ids); - Original Message - From: Webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 11:38 AM Subject: SQL help How do I write SQL in MySQL to randomly select 5 records from a table? - 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: Fw: SQL HELP
select *,DATE_FORMAT(datecolumn,'%M %D %Y'); On Tue, 16 Jan 2001, Mike Podlesny wrote: Thanks for your help but that unfortunately doesn't answer the question, unless I am looking at it wrong. I want the sql statement to read something to the affect: SELECT * FROM Table * is about 40 fields, which I do not want to type all of them out so do i do the following? SELECT DATE_FORMAT(*,'%M %D %Y') - 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: SQL HELP
"j.urban" writes: select field1,field2,DATE_FORMAT(datefield,"%M %d, %Y"),field4,fieldn from table; He didn't want to have to explicitly list all 40 other fields, though. Can't he do something like SELECT *.DATE_FORMAT(datefield, "%M %d, %Y") FROM table; ? That gives him one extra field, and he can just ignore the first datefield. --Cindy - 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
