I?m trying to rewrite a SQL to use with MySQL 4.0 version (no subqueries).

The database structure is as follows:


Book
------------
ID
Title
Author


WordRel
-------------
WordIdxID
BookID


WordIdx
-------------
WordIdxID
Word


SELECT Book.Title 
FROM WordIdx, WordRel, Book 
WHERE Book.ID=WordRel.BookID 
    AND WordRel.WordIdxID=WordIdx.WordIdxID 
    AND WordIdx.Word=:Word1
    AND WordRel.BookID IN 
           (SELECT WordRel.BookID 
             FROM WordRel, WordIdx 
             WHERE Word.Rel.WordClave=WordIdx.WordIdxID 
             AND WordIdx.Word=:Word2)
ORDER BY Book.Title ASC


This is a database that keeps records of Books, and this query is
intended to search for books that contains the words word1 and word2. 
I have this query running on MSSQL.  I am trying to get it running on
MySQL but the version that is installed on my server does not support
subqueries.

Questions:

1. Is it possible to rewrite this query with no subqueries???
2. If so, how can I include a word3 to the query???


Thank you


José Gutiérrez

Try this:

SELECT Book.Title 
FROM WordRel as wr
LEFT JOIN WordIdx as wi ON (wi.WordIdxID=wr.WordIdxID)
LEFT JOIN Book as b ON (b.id=wr.BookID)
WHERE wi.Word IN('Word1','Word2')
ORDER BY b.Title ASC;

Hum, sorry, I think this will work better:

SELECT b.Title 
FROM WordIdx as wi
LEFT JOIN WordRel as wr ON (wr.WordIdxID=wi.WordIdxID)
LEFT JOIN Book as b ON (b.id=wr.BookID)
WHERE wi.Word IN('Word1','Word2')
ORDER BY b.Title ASC;

I'm going to assume two things:
(1) That the :Word1 and :Word2 markers are memory variables. These
would be written as @Word1 and @Word2 in MySQL.
(2) That the expression Word.Rel.WordClave=WordIdx.WordIdxID in the
subquery should actually be WordRel.WordIdxID=WordIdx.WordIdxID as in
your main query (since you haven't defined a table called Word.Rel or
a column called WordClave anywhere).

The previous commenter's queries will get books that match _any_ of
the search words. This query will get books that match _all_ of the
search words:

SELECT Book.Title 
FROM Book, 
    WordIdx wi1, WordRel wr1, 
    WordIdx wi2, WordRel wr2,
    WordIdx wi3, WordRel wr3 (, ...) 
WHERE Book.ID=Wr1.BookID 
    AND Wr1.WordIdxID=Wi1.WordIdxID AND Wi1.Word=@Word1
    AND Wr2.BookID=Wr1.BookID 
    AND Wr2.WordIdxID=Wi2.WordIdxID AND Wi2.Word=@Word2
    AND Wr3.BookID=Wr1.BookID 
    AND Wr3.WordIdxID=Wi3.WordIdxID AND Wi3.Word=@Word3
    (...)
ORDER BY Book.Title ASC

You may be able to speed up this query by using a single table instead
of the WordRel and WordIdx tables. This table would have the fields
Word and BookID. You wouldn't waste that much extra space by repeating
the words for each book they appear in, and this would allow MySQL to
search a single table, instead of cross-linking two. Very likely it
could return the results it needed directly from the table index,
without ever looking into the table itself.