Help with mySQL query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
stsr11
Forum Newbie
Posts: 17
Joined: Thu Jul 15, 2004 6:57 pm

Help with mySQL query?

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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%'
ldomingues
Forum Commoner
Posts: 41
Joined: Fri Aug 06, 2004 1:15 pm
Location: Portugal

Post by ldomingues »

Just a thought:
Is that a "library" or a bookstore?

Libraries often use standard formats for book record representation (MARC formats).
stsr11
Forum Newbie
Posts: 17
Joined: Thu Jul 15, 2004 6:57 pm

Post 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...
Post Reply