MySQL sub-query error.
Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL sub-query error.
I don't think Sub queries like the = and != operators as groupid will never equal the result of a multi row subquery, but it can be IN a list of results. Just to check, you are using 4.1+ right? I think this alteration would work: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id IN (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id NOT IN (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = That being said, I think you can do it without Subqueries with this query: SELECT group_id, name, IF(1=userid,'SELECTED',' ') FROM groups ORDER BY group_id Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:03 AM To: [EMAIL PROTECTED] Subject: MySQL sub-query error. Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL sub-query error.
If it's below 4.0 then you can't use subqueries at all, subqueries were (will be) introduced in 4.1. That query is just a standard query, but the third item will return the value of 'SELECTED' if the userid is equal to 1 and ' ' otherwise. Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 10:00 AM To: Chris Subject: Re: MySQL sub-query error. See..I changed it = only coz the IN wouldn't work. I am guessing its a MySQL version issue. I am using a version below 4.0. I am curious about the second version of my query you wrote ? Can you elaborate ? Chris wrote: I don't think Sub queries like the = and != operators as groupid will never equal the result of a multi row subquery, but it can be IN a list of results. Just to check, you are using 4.1+ right? I think this alteration would work: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id IN (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id NOT IN (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = That being said, I think you can do it without Subqueries with this query: SELECT group_id, name, IF(1=userid,'SELECTED',' ') FROM groups ORDER BY group_id Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:03 AM To: [EMAIL PROTECTED] Subject: MySQL sub-query error. Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- 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]
