Re: Nested SELECT statements problem
Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Nested SELECT statements problem
Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Le dimanche, 3 aoû 2003, à 12:41 Canada/Eastern, Pascal Délisle a écrit : Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Nested SELECT statements problem
- Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Can you dynamically generate a unique name for your temporary table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Since I'm new to mySQL, could you point me how to create temporary tables dynamically? Le dimanche, 3 aoû 2003, à 15:32 Canada/Eastern, Jim McAtee a écrit : - Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Can you dynamically generate a unique name for your temporary table? -- 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: Nested SELECT statements problem
| Matthew | Gold | ++-+--+ 3 rows in set (0.02 sec) - Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 8:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Le dimanche, 3 aoû 2003, à 12:41 Canada/Eastern, Pascal Délisle a écrit : Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.506 / Virus Database: 303 - Release Date: 01/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
In the last episode (Aug 03), Pascal Dlisle said: Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: ... The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Use the CREATE TEMPORARY TABLE command, which creates tables which are invisible to other client connections and are automatically dropped when the client exits. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
What version of MySQL are you using? Regards, A$ On Saturday, August 2, 2003, at 11:45 PM, Pascal Délisle wrote: Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Nested SELECT statements problem
In the last episode (Aug 02), Pascal Dlisle said: I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. I doubt think it would work under Oracle either, since you quoted your subselect. Unless book.IDLivre is a varchar field with one of the records containing the string SELECT book.IDLivre ... = ecr.IDLivre, of course. Also make sure you're using MySQL 4.1.0, since that's the first version that supports subselects. If you're running something older, take a look at http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html which shows you how to rewrite most (not all) subqueries as joins. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Dan Nelson wrote: In the last episode (Aug 02), Pascal Dlisle said: I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. I doubt think it would work under Oracle either, since you quoted your subselect. Unless book.IDLivre is a varchar field with one of the records containing the string SELECT book.IDLivre ... = ecr.IDLivre, of course. Also make sure you're using MySQL 4.1.0, since that's the first version that supports subselects. If you're running something older, take a look at http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html which shows you how to rewrite most (not all) subqueries as joins. How about if you try this: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM auteur aut INNER JOIN (livreEcritPar ecr INNER JOIN livre book ON ecr.IDLivre = book.IDLivre) ON aut.IDAuteur = ecr.IDAuteur WHERE book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail)
RE: Nested SELECT statements problem
One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
