SELECT with OR including tables
I have a database with three tables: books (with details about the book, PK is the CopyID), keywords (list of keywords, PK is the id), and KeywordsLink, which is a table of links of many of the many books and keywords with Field IDs, BookID and KeywordID.
I am trying to create an advanced search form in my application where you can search by various criteria. At the moment I have a job with Title, Author and Publisher (all from the Book table). It creates SQL as:
SELECT * FROM Books WHERE Title Like '%Software%' OR Author LIKE '%Spolsky%';
I want to expand this search as well as search with tags - mainly to add another OR clause to search for tags. I tried to do it by doing the following
SELECT *
FROM Books, Keywords, Keywordslink
WHERE Title LIKE '%Joel%'
OR (Name LIKE '%good%' AND BookID=Books.CopyID AND KeywordID=Keywords.ID)
I thought that using parentheses might separate the 2nd part from my kinda sentence, so joining was only appreciated in that part, but it looks like it doesn't. This all gives me a long list of multiple copies of the same book that suits a bit Title LIKE '%Joel%'
.
Is there a way to do this using pure SQL or will I have to use two SQL statements and combine them in my application (remove duplicates in the process).
I'm using MySQL at the moment if it matters, but the application is using ODBC and I'm hoping to make it DB agnostic (maybe even use SQLite eventually, or have it so the user can choose which DB to use).
source to share
What you did here made a Cartesian result set by linking tables with commas but not having any join criteria. Switch your statements to use external join operators and that should allow you to reference keywords. I don't know your schematic, but maybe something like this will work:
SELECT
*
FROM
Books
LEFT OUTER JOIN KeywordsLink ON KeywordsLink.BookID = Books.CopyID
LEFT OUTER JOIN Keywords ON Keywords.ID = KeywordsLink.KeywordID
WHERE Books.Title LIKE '%JOEL%'
OR Keywords.Name LIKE '%GOOD%'
source to share
You need to join 3 tables together, which gives you a tabular result set. Then you can check any columns you like and make sure you get great results (i.e. no duplicates).
Like this:
select distinct b.*
from books b
left join keywordslink kl on kl.bookid = b.bookid
left join keywords k on kl.keywordid = k.keywordid
where b.title like '%assd%'
or k.keyword like '%asdsad%'
You should also try to avoid triggering LIKE values with a percent sign (%), as this means SQL Server cannot use an index on that column and must perform a full (and slow) scan of the table. It starts making the request in the "starts with" request.
Perhaps also consider the options for full text search in SQL Server.
source to share
Use UNION
.
(SELECT Books.* FROM <first kind of search>)
UNION
(SELECT Books.* FROM <second kind of search>)
The point is that you can write two (or more) simple and efficient queries instead of one complex query that tries to do everything at once.
If the number of resulting rows is small, then there UNION
will be very little overhead (and you can use the faster UNION ALL
one if you don't have duplicates or don't care).
source to share