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!
Help with mySQL query?
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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%'-
ldomingues
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 06, 2004 1:15 pm
- Location: Portugal
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...
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...