List:General Discussion« Previous MessageNext Message »
From:Pascal Délisle Date:August 3 2003 4:41pm
Subject:Re: Nested SELECT statements problem
View as plain text  
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

Thread
Nested SELECT statements problemPascal Délisle3 Aug
  • Re: Nested SELECT statements problemDan Nelson3 Aug
    • Re: Nested SELECT statements problemInc Eternal Designs3 Aug
  • RE: Nested SELECT statements problemLin Yu3 Aug
    • Re: Nested SELECT statements problemPascal Délisle3 Aug
      • Re: Nested SELECT statements problemPascal Délisle3 Aug
        • Re: Nested SELECT statements problemJim McAtee3 Aug
          • Re: Nested SELECT statements problemPascal Délisle3 Aug
        • Re: Nested SELECT statements problemMatthew McNicol3 Aug
        • Re: Nested SELECT statements problemDan Nelson3 Aug
  • Re: Nested SELECT statements problemAdam Fortuno4 Aug