List:General Discussion« Previous MessageNext Message »
From:Inc Eternal Designs Date:August 3 2003 5:01am
Subject:Re: Nested SELECT statements problem
View as plain text  

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)


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