Re: Inner selects
Michael Beaucourt wrote: Hi all, I was wondering if it is possible to perform an inner select in MySQL. I have a database called EMP and when i enter the following query, I keep getting error 1064: select ename,deptno from emp where deptno in (select deptno from emp where ename = 'SCOTT') The error message reads as follows: ERROR 1064: You have an error in your SQL syntax near 'select deptno from emp where ename='scott')' at line 1 You can achieve it by writing this query in the following way: select a.ename,a.deptno from emp a,emp b where a.deptno=b.deptno and b.ename='SCOTT'; MySQL doesn't support subselects; -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - 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: Inner selects
no mysql right now (3.23) does not support inner selects. however, it looks like you could join the 2 tables to get the result you want I'm assuming there are 2 tables inside the emp database ? or is emp the table ? anyway here is an idea: select d1.ename, d1.deptno FROM emp.table1 d1, emp.table2 d2 WHERE d1.deptno = d2.deptno AND d2.ename = 'SCOTT' -- Andrew - Original Message - From: Michael Beaucourt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 27, 2001 11:27 AM Subject: Inner selects Hi all, I was wondering if it is possible to perform an inner select in MySQL. I have a database called EMP and when i enter the following query, I keep getting error 1064: select ename,deptno from emp where deptno in (select deptno from emp where ename = 'SCOTT') The error message reads as follows: ERROR 1064: You have an error in your SQL syntax near 'select deptno from emp where ename='scott')' at line 1 Does MySQL support inner select this way or is there another way to achieve the same? Thanks for your help. Best Regards, Michael Beaucourt -- - 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: Inner selects
Michael Beaucourt wrote: Hi all, I was wondering if it is possible to perform an inner select in MySQL. I have a database called EMP and when i enter the following query, I keep getting error 1064: select ename,deptno from emp where deptno in (select deptno from emp where ename = 'SCOTT') The error message reads as follows: ERROR 1064: You have an error in your SQL syntax near 'select deptno from emp where ename='scott')' at line 1 You can achieve it by writing this query in the following way: select a.ename,a.deptno from emp a,emp b where a.deptno=b.deptno and b.ename='SCOTT'; MySQL doesn't support subselects; -- _/_/ _/_/_/ - Rafa? Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- Hi, Thanks for the answer. I already knew this was another solution for the query, but I was actually just wondering if MySQL supported inner select. Now I have some other questions also (I'm having a test for school tomorrow and school is using Oracle and I am using MySQL ;): - In oracle you have outer joins, where you have to use a (+) to find rows with null values and stuff like that. Any suggestions on how to do this in MySQL? - The function UNION to connect 2 select queries also doesn't seem to work in MySQL. Any workarounds? - In Oracle there is a very powerful DECODE(expr1,searchstring1,replacestring1,[searchstring2,replacestring2],[default]) but apparently decode means something else in MySQL. Is there a comparable function in MySQL? Thanks. Michael -- - 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: Inner selects
Hi, - In oracle you have outer joins, where you have to use a (+) to find rows with null values and stuff like that. Any suggestions on how to do this in MySQL? You must use the Left Join type syntax in the FROM clause of your query. - The function UNION to connect 2 select queries also doesn't seem to work in MySQL. Any workarounds? I believe, not yet supported in MySQL. - In Oracle there is a very powerful DECODE(expr1,searchstring1,replacestring1,[searchstring2,replacestring2],[ default]) but apparently decode means something else in MySQL. Is there a comparable function in MySQL? Yes: It's the same as the MSSQL70 function. CASE expr1 WHEN searchstring1 THEN replacestring1 [WHEN searchstring2 THEN replacestring2] [ELSE default] END Good luck, Bruce - 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: Inner selects
On Mon, Aug 27, 2001 at 07:28:24PM +0200, Bruce Stewart wrote: - The function UNION to connect 2 select queries also doesn't seem to work in MySQL. Any workarounds? I believe, not yet supported in MySQL. It's was in the 4.0 tree last I looked, so it won't be long. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 10 days, processed 123,720,614 queries (137/sec. avg) - 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
