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]
