Page 1 of 1

Help with mySQL query?

Posted: Wed Sep 22, 2004 10:54 am
by stsr11
I am trying to perform an SQL query on a book library and my SQL is not that good - can anyone help? I have 3 tables - books, subjects(keywords) and booksubjects(keywords associated with books). I want to return a result which searches Book.Title(string) and Subjects.Subject(string) for the occurence of '%word%', so I came up with:

їcode]select * from Books B,Subjects S,BooksSubjects BS
where B.ISBN = BS.ISBN and BS.SubjectID = S.SubjectID and
B.Title LIKE '%word%' or S.Subject LIKE '%word%'ї/code]

My problem is that some books don't have an associated BookSubject record and therefore do not generate a result to search. I have looked at LEFT JOIN but don't know how to perform one across 3 tables...help please!

Posted: Wed Sep 22, 2004 11:02 am
by feyd
maybe (untested):

Code: Select all

SELECT * FROM Books b, Subjects s
LEFT JOIN BookSubjects bs ON b.ISBN = bs.ISBN AND s.SubjectID = bs.SubjectID
WHERE b.Title LIKE '%word%' OR s.Subject LIKE '%word%'

Posted: Wed Sep 22, 2004 12:18 pm
by ldomingues
Just a thought:
Is that a "library" or a bookstore?

Libraries often use standard formats for book record representation (MARC formats).

Posted: Wed Sep 22, 2004 11:49 pm
by stsr11
That worked great, but it turned out be a bit time consuming (6000+ books, 3000+ keywords, 20000+ keyword associations - 10 to 12 seconds) so I have decided to create a temporary table and add records according to 3 different (but much simpler) queries, all of which execute in a fraction of a second. ie. Add all records where Title LIKE '%word%', then add all records where Subject LIKE '%word%' and booksubject(ISBN,SubjectID) association exists - ORDER BY ISBN.

On the subject of MARC formats, Yes, it is a library, but it will only be used by me and I don't have any need for need for MARC records - good thought though.

Thx for your help guys...