Re: Nested SELECT statements problem

[Pascal Délisle]

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:masterpace@stripped]
>> Sent: Saturday, August 02, 2003 11:46 PM
>> To: mysql@stripped
>> 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/mysql?unsublinyu@ style="color:#666">stripped
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
>