List:General Discussion« Previous MessageNext Message »
From:Lin Yu Date:August 3 2003 5:07am
Subject:RE: Nested SELECT statements problem
View as plain text  
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?unsub=1

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