Re: Nested SELECT statements problem

[Eternal Designs, Inc]

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)