RE: SELECT subquery problem
You can do: SELECT last_name, first_name, phone, if(pub_email=Y,email,) as email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC Gracias, Carlos. This worked fine! ---Fritz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SELECT subquery problem
De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com WillsChill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SELECT subquery problem
Try using a CASE construct in the select. Should work for this. A On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn stef...@web.de wrote: You cannot do this. A sql result alwas has the same number of columns in each row. You could have null or in the column, though. This could be done via the if(,,)-statement of mysql or by using a union and two selects, one for pub_email=n and the other for the rest. Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr Von: cl c...@nimbleeye.com An: mysql@lists.mysql.com Betreff: SELECT subquery problem De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com Wills Chill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Aw: SELECT subquery problem
You cannot do this. A sql result alwas has the same number of columns in each row. You could have null or in the column, though. This could be done via the if(,,)-statement of mysql or by using a union and two selects, one for pub_email=n and the other for the rest. Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr Von: cl c...@nimbleeye.com An: mysql@lists.mysql.com Betreff: SELECT subquery problem De-lurking here. I am trying to figure out how to return results from a query. What I need to do is to return 4 columns from a database. This is easy: SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC This works fine, as expected. But, I want to only display the value in `email` if the value in another field, `pub_email` = Y So, the resultant output would look like this, for instance, if the value of `pub_email` =N for Mr. Wills: Jones John 555-555- johnjo...@nowhere.com Smith Jim 555-222- jimsm...@nothing.com Wills Chill 555-111- Zorro Felicity 555-999- felicityzo...@madeup.com Can't quite figure out how to express this. TIA for your suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Subquery Problem With mysql-4.0 (Works with mysql-5.0)
I face one problem with mysql4.0 I've two tables, one called company and the other called favorites. The relation between those two tables is CO_ID on the company table and MASTER_CO_ID on the favorites table. When I run the following query it works fine on mysql-5.0 but it give error with mysql-4.0. Query** SELECT c.MASTER_CO_ID as Expr1 FROM company LEFT OUTER JOIN (select MASTER_CO_ID,SLAVE_CO_ID from favorites where MASTER_CO_ID = '1' GROUP BY MASTER_CO_ID,SLAVE_CO_ID) c ON company.CO_ID = c.SLAVE_CO_ID GROUP BY c.MASTER_CO_ID *** Error with mysql4.0 #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select MASTER_CO_ID,SLAVE_CO_ID from favorites where MAS *** I have two tables company and favorites, like CREATE TABLE `company` ( `CO_ID` varchar(36) NOT NULL default '', `CO_NAME` varchar(50) default NULL, PRIMARY KEY (`CO_ID`), FULLTEXT KEY `CO_NAME` (`CO_NAME`,`INDUSTRY_NAME`,`CO_ADDR1`,`CO_ADDR2`,`CO_PIN`,`CO_URL`,`CO_INFO`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `favorites` ( `FAV_ID` bigint(20) NOT NULL auto_increment, `MASTER_CO_ID` varchar(36) default NULL, `SLAVE_CO_ID` varchar(36) default NULL, PRIMARY KEY (`FAV_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
Re: Subquery Problem With mysql-4.0 (Works with mysql-5.0)
2006/6/5, MySQL Nexeia [EMAIL PROTECTED]: I face one problem with mysql4.0 I've two tables, one called company and the other called favorites. The relation between those two tables is CO_ID on the company table and MASTER_CO_ID on the favorites table. When I run the following query it works fine on mysql-5.0 but it give error with mysql-4.0. Query** SELECT c.MASTER_CO_ID as Expr1 FROM company LEFT OUTER JOIN (select MASTER_CO_ID,SLAVE_CO_ID from favorites where MASTER_CO_ID = '1' GROUP BY MASTER_CO_ID,SLAVE_CO_ID) c ON company.CO_ID = c.SLAVE_CO_ID GROUP BY c.MASTER_CO_ID *** MySQL 4.0.x does not support subquery. Subqueries are only supported starting with 4.1. Error with mysql4.0 #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select MASTER_CO_ID,SLAVE_CO_ID from favorites where MAS *** I have two tables company and favorites, like CREATE TABLE `company` ( `CO_ID` varchar(36) NOT NULL default '', `CO_NAME` varchar(50) default NULL, PRIMARY KEY (`CO_ID`), FULLTEXT KEY `CO_NAME` (`CO_NAME`,`INDUSTRY_NAME`,`CO_ADDR1`,`CO_ADDR2`,`CO_PIN`,`CO_URL`,`CO_INFO`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `favorites` ( `FAV_ID` bigint(20) NOT NULL auto_increment, `MASTER_CO_ID` varchar(36) default NULL, `SLAVE_CO_ID` varchar(36) default NULL, PRIMARY KEY (`FAV_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ; -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update with subquery problem
Subqueries are not supported until mysql 4.1. I'm guessing you have an earlier version. With version 4.0.4 or later, you can accomplish the same thing with a multi-table update: UPDATE tbl1, tbl2 SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl1.ID = tbl2.ID AND tbl2.status='Active'; or equivalently UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl2.status='Active'; See the manual for more http://dev.mysql.com/doc/mysql/en/UPDATE.html. Michael prolist wrote: I am trying to update a related table with a subquery. This is what I am using - update tbl1 set col1=col1+1 where ID IN (select ID from tbl2 where status='Active'); But I get syntax error. I am not much of a database guy, so can't understand what am I doing incorrectly. Can someone help? TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update with subquery problem
Manish, What version of MySQL are you using? The chances are subqueries are not supported in your version. Try restructuring your query as a join like: UPDATE tbl1, tbl2 SET tbl1.col1=tbl1.col1+1 WHERE tbl.ID = tbl2.ID AND tbl2.status='Active' http://dev.mysql.com/doc/mysql/en/UPDATE.html Regards, Lachlan -Original Message- From: prolist [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 3:52 PM To: [EMAIL PROTECTED] Subject: Update with subquery problem I am trying to update a related table with a subquery. This is what I am using - update tbl1 set col1=col1+1 where ID IN (select ID from tbl2 where status='Active'); But I get syntax error. I am not much of a database guy, so can't understand what am I doing incorrectly. Can someone help? TIA, - Manish -- 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]
Update with subquery problem
I am trying to update a related table with a subquery. This is what I am using - update tbl1 set col1=col1+1 where ID IN (select ID from tbl2 where status='Active'); But I get syntax error. I am not much of a database guy, so can't understand what am I doing incorrectly. Can someone help? TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery problem.
Hi, My server version : 4.0.18-max-nt I have created 2 tables 'channels' and 'users' in my database. I can query these tables individually. I am trying to execute a query with 'exists' clause. mysql select * from channels where exists (select * from users); This give the following error. ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'exists (sele ct * from users)' at line 1 I am getting this syntax error message. The query seems perfect to me. Any clue what is happening? Help! -Nambi __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery problem.
Version 4.0 doesn't support subqueries. In any event, your query would return all rows from channels as long as there are any rows in users, which I doubt is what you intended. --- nambi c [EMAIL PROTECTED] wrote: Hi, My server version : 4.0.18-max-nt I have created 2 tables 'channels' and 'users' in my database. I can query these tables individually. I am trying to execute a query with 'exists' clause. mysql select * from channels where exists (select * from users); This give the following error. ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'exists (sele ct * from users)' at line 1 I am getting this syntax error message. The query seems perfect to me. Any clue what is happening? Help! -Nambi __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- 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: subquery problem.
Subqueries aren't implemented in 4.0 - only 4.1 and upwards support subqueries. Cheers Andrew. - Original Message - From: nambi c [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 22, 2004 10:31 PM Subject: subquery problem. Hi, My server version : 4.0.18-max-nt I have created 2 tables 'channels' and 'users' in my database. I can query these tables individually. I am trying to execute a query with 'exists' clause. mysql select * from channels where exists (select * from users); This give the following error. ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'exists (sele ct * from users)' at line 1 I am getting this syntax error message. The query seems perfect to me. Any clue what is happening? Help! -Nambi __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- 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]
