I've got a system which enables me to deal with books and also the researches people can perform when they're searching for books.
The database is mysql, and that i have two primary tables
BOOKS(id *int*, author *string*, title *string*, price *string*, tags *set('sci-fi', 'literature', 'theatre', 'drama', 'romance', 'recent', ...)*, ...) RESEARCHES is (researches_id, price_max, author, ...)
After I give a book during my database, I wish to obtain the researches matching for this new book.
As people may use many tags within their researches (a lot more than 30), I made the decision to produce another table, and never to utilize a tags column in
RESEARCHES which will be a
because In my opinion there's no index on the
SET column, and thus my database can't quickly handle my request.
RESEARCHES_TAGS(id, researches_id, tag).
Basically give a book by P.K Dick, using the tags 'sci-fi', 'drama', I wish to obtain the researches with tag as
NULL or tag in ('sci-fi', 'drama') and also to exclude
researches along with other tags.
So I am doing the next request:
SELECT * FROM RESEARCHES LEFT JOIN RESEARCHES_TAGS ON RESEARCHES.researches_id = RESEARCHES_TAGS.researches_id AND RESEARCHES_TAG.tag IN ('literature', 'theatre', 'romance', ...) WHERE RESEARCHES_TAGS.tag IS NULL
Observe that the required tags array does not retain the tags 'sci-fi', 'drama'.
My real question is: it is possible to better solution?
Thanks ahead of time,
You need to keep the
JOIN criteria outside of your
WHERE criteria. Also, an
INNER JOIN will work better in my experience, because you will not possess a
RESEARCHES_TAGS row with no corresponding
RESEARCHES row... right?
SELECT * FROM RESEARCHES INNER JOIN RESEARCHES_TAGS ON RESEARCHES.researches_id = RESEARCHES_TAGS.researches_id WHERE RESEARCHES_TAGS.tag IS NULL OR tag IN ('literature', 'theatre', 'romance', ...)
Also... you need to clarify this question to point which table
tag goes to.
I believe the thing is to exclude the rows related to tags 'literature', 'theatre', 'romance', etc. and only rows without criteria, or with matching criteria (sci-fi or drama or both). The query you intend does the alternative.
I myself usually complements the
LEFT JOIN j WHERE j.id IS NULL statement, but it's clearly a counter-intuitive solution.
Maybe changing the tables structure allows a less complicated and/or faster query? Let us rephrase the issue...
Will asks for any products Jane asks for products with at least A and B John asks for products with at least C Yael asks for products with at least A and C Mark asks for products with at least A
You own an item that fits A and B needs. Who're you to definitely market it to ?
The solution being Will, Jane and Mark, how can you place it in tables and query ?
Should you have only a couple of kinds of needs, you place a area for every one, and request for
WHERE C = 0 AND D = 0, since you don't want to make contact with individuals who require something you don't have. But what for those who have an increasing listing of criteria ? You cannot just keep adding fields. And taking advantage of a collection area would exclude the chance to utilize a catalog.