case expression
Hello all ... be gentle, I'm new at this: I've got a simple case query. One works (the top one), but when I try to add additional columns to the query it fails. I reckon it's a pretty simple mistake I'm making, but I don't know what. Any help is appreciated. - This works ... SELECT CASE WHEN CHARACTER_LENGTH(system) 65 then CONCAT(LEFT(system, 60), ...) ELSE system END -- This doesn't ... SELECT CASE WHEN CHARACTER_LENGTH(system) 65 then CONCAT(LEFT(system, 60), ...), wsh_year, id ELSE system, wsh_year, id END FROM wsh Maybe an IF statement would work better here? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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 mysql-unsubscribe- [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: case expression
Thanks, that works fine. If I wanted to create an alias for the column, how would I do that? The first select statement (before the Else) is the current column name. When I add 'AS system' to the end of the string, it fails. Ex. SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system AS system END, wsh_year, id FROM wsh Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:34 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Put the other fields after the CASE statement: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END, wsh_year, id FROM wsh The CASE statement just returns 1 field. - 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: case expression
Makes sense. Thanks for the help! Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:51 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Consider the CASE statement to be a function, that can only return values. Aliases would go outside the CASE STATEMENT, as follows: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END AS system, wsh_year, id FROM wsh -Original Message- From: Richard Forgo [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 3:44 PM To: Jon Wagoner; 'MySQL Mailing List' Subject: RE: case expression Thanks, that works fine. If I wanted to create an alias for the column, how would I do that? The first select statement (before the Else) is the current column name. When I add 'AS system' to the end of the string, it fails. Ex. SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system AS system END, wsh_year, id FROM wsh Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:34 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Put the other fields after the CASE statement: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END, wsh_year, id FROM wsh The CASE statement just returns 1 field. - 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
simple (I thought) delete question
Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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: Newbie: Intersecting entities
John, Michael, Andy and James, Thanks for the informative responses. I was hoping I was on the right track. Thanks for the query help, too. It looks like this is going to work just fine. Cheers, Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: Thursday, November 07, 2002 10:16 AM To: [EMAIL PROTECTED]; Richard Forgo Subject: RE: Newbie: Intersecting entities aha! sure. you weren't being vague. you just needed to educate me. some of us have used intersecting entities through the years, but didn't know that there was a fancy name for what we were doing. if my memory weren't so bad, i'd use it to impress people in the next interview. in this case, simply do a three way multiple join with the intersecting entity in the middle. something like this: select table1.col2, table 3.col2 from table1 left join table2 on table1.col1 = table2.col1 left join table3 on table2.col2 = table3.col1 or you could use an equal join with the intersecting entity in the middle. if you have a windows box that you can use as a front end, i strongly recommend that you download corereader because its point and click queries can make a six way join look simple. then, it'll show you the sql code. best of all is that it's free. http://corereader.com Sorry for being vague. I think the closest thing I can find to it is, possibly, a three-way join. I'm trying to figure out how to write the query. I've got the following tables: systems sys_nameID Abrams 202 Patriot 544 Stinger 229 ... and ... new_req proj_name ID Test Bed Alpha 344 Tracked Wheels 989 Battle Monitor 823 Shoulder Harness654 Ammo Flask 454 Spotter 773 There may be multiple new requirements for a single system. To accommodate them, I created a new table -- an intersecting entity is what I recall it being called -- to bridge the two. I can't keep the multiple references to new_req in the systems table, nor can I keep them in the new_req table. As best I can figure, I need to have a bridge table ... e.g., ie_sys_req sys_id proj_name_id 202344 202989 202823 544654 544454 544773 If this seems peculiar, have patience with me I'm new at this. If it's right, let me know so I can breath a sigh of relief. And if the structure is right, could someone offer some guidance on how a query that allows me to query new_req using the intersecting entity (or three-way join, whatever it's called) would be written. Thanks, Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: Wednesday, November 06, 2002 6:55 PM To: [EMAIL PROTECTED]; Richard Forgo Subject: Re: Newbie: Intersecting entities if a succinct statement is possible, perhaps you could give us an idea of its nature? somebody may be familiar with the concept under a different name. I'm trying to find some documentation on creating intersecting entities in MySQL, but haven't been able to track any info down on the MySQL site or on the web. At least we called them intersecting entities while I was in Oracle training (which was some time ago). I think I remember how to set them up, but I'd love to have something to refer to quickly before I start. Can anyone point me in the right direction? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com
Newbie: Intersecting entities
I'm trying to find some documentation on creating intersecting entities in MySQL, but haven't been able to track any info down on the MySQL site or on the web. At least we called them intersecting entities while I was in Oracle training (which was some time ago). I think I remember how to set them up, but I'd love to have something to refer to quickly before I start. Can anyone point me in the right direction? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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: Newbie: Intersecting entities
Sorry for being vague. I think the closest thing I can find to it is, possibly, a three-way join. I'm trying to figure out how to write the query. I've got the following tables: systems sys_nameID Abrams 202 Patriot 544 Stinger 229 ... and ... new_req proj_name ID Test Bed Alpha 344 Tracked Wheels 989 Battle Monitor 823 Shoulder Harness654 Ammo Flask 454 Spotter 773 There may be multiple new requirements for a single system. To accommodate them, I created a new table -- an intersecting entity is what I recall it being called -- to bridge the two. I can't keep the multiple references to new_req in the systems table, nor can I keep them in the new_req table. As best I can figure, I need to have a bridge table ... e.g., ie_sys_req sys_id proj_name_id 202344 202989 202823 544654 544454 544773 If this seems peculiar, have patience with me I'm new at this. If it's right, let me know so I can breath a sigh of relief. And if the structure is right, could someone offer some guidance on how a query that allows me to query new_req using the intersecting entity (or three-way join, whatever it's called) would be written. Thanks, Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: Wednesday, November 06, 2002 6:55 PM To: [EMAIL PROTECTED]; Richard Forgo Subject: Re: Newbie: Intersecting entities if a succinct statement is possible, perhaps you could give us an idea of its nature? somebody may be familiar with the concept under a different name. I'm trying to find some documentation on creating intersecting entities in MySQL, but haven't been able to track any info down on the MySQL site or on the web. At least we called them intersecting entities while I was in Oracle training (which was some time ago). I think I remember how to set them up, but I'd love to have something to refer to quickly before I start. Can anyone point me in the right direction? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [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: a newserver would be better than this mailing list
I'll try not to extend this thread any more than it needs (it's gone pretty far now). If you want to view the list via Usenet newsgroup, just do it. It's been available on my service provider for several months at mailing.database.mysql. If you prefer via email, fine. Rik Forgo JIST3 Army Test Training Investment Conference (ATTIC) Diverse Technologies Corp. (c) 443.463.8571 - 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: Impact of Free ORACLE
Did Big Larry go open source? This is news to me. If you have more details, please provide the list a link to share that info. Rik Forgo JIST3 Army Test Training Investment Conference (ATTIC) Diverse Technologies Corp. (c) 410.852.7420 (h) 410.859.8474 -Original Message- From: MadProfessor [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 5:48 PM To: [EMAIL PROTECTED] Subject: Impact of Free ORACLE Just curious what everyone thinks the impact on MySQL will be now that Oracle is free? - 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
