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 and RESEARCHES.

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 SET, 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,

Marc

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.