StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSELECT with ORs including table joins
text
Body
copied!<p>I've got a database with three tables: Books (with book details, PK is CopyID), Keywords (list of keywords, PK is ID) and KeywordsLink which is the many-many link table between Books and Keywords with the fields ID, BookID and KeywordID.</p> <p>I'm trying to make an advanced search form in my app where you can search on various criteria. At the moment I have it working with Title, Author and Publisher (all from the Book table). It produces SQL like:</p> <pre><code>SELECT * FROM Books WHERE Title Like '%Software%' OR Author LIKE '%Spolsky%'; </code></pre> <p>I want to extend this search to also search using tags - basically to add another OR clause to search the tags. I've tried to do this by doing the following</p> <pre><code>SELECT * FROM Books, Keywords, Keywordslink WHERE Title LIKE '%Joel%' OR (Name LIKE '%good%' AND BookID=Books.CopyID AND KeywordID=Keywords.ID) </code></pre> <p>I thought using the brackets might separate the 2nd part into its own kinda clause, so the join was only evaluated in that part - but it doesn't seem to be so. All it gives me is a long list of multiple copies of the one book that satisfies the <code>Title LIKE '%Joel%'</code> bit.</p> <p>Is there a way of doing this using pure SQL, or would I have to use two SQL statements and combine them in my app (removing duplicates in the process).</p> <p>I'm using MySQL at the moment if that matters, but the app uses ODBC and I'm hoping to make it DB agnostic (might even use SQLite eventually or have it so the user can choose what DB to use).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload