CODEWITHSUNDEEP
mysql
Shaun
Shaun

Reputation: 2181

How to exclude results within this MySQL query

I'm setting up a toxi tagging system for some articles, and am using this query as a starting point for testing...

SELECT a.*
FROM tagmap at, articles a, tag t, userExcludedArticles uea
WHERE at.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND a.id = at.articleID
GROUP BY a.id

If I have a table called userExcludedArticles (id, userID, articleID), which stores the id of articles a user has flagged as not wanting to see, how do I include that in the above query?

For testing, I have two articles in total stored in my articles table, and added both of their ids to the userExcludedArticles for userID 1 - ie this user has effectively said they don't want to be shown those two articles.

I added the following line to the query...

AND (uea.userID='1' AND a.id <> uea.articleID)

To give...

SELECT a.*
FROM tagmap at, articles a, tag t, userExcludedArticles uea
WHERE at.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND a.id = at.articleID
AND (uea.userID='1' AND a.id <> uea.articleID)
GROUP BY a.id

But both articles appear when searching, instead of no articles.

How do I get it to return all articles that match the included tags, while excluding any articles the user has flagged as not wanting to see?

Thanks for your time and help.

Upvotes: 0

Views: 31

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

First, don't use GROUP BY when you don't use aggregates in the results. MySQL allows you to use it without aggregates, but it produces random results. I assume you're using it here because including userExcludedArticles table gave you duplicates of the rows. That's not the proper way to handle it.

As your query is now written it will check every row in userExcludedArticles against every row in articles/tag/tagmap and if there is a row which doesn't have the article id it can be shown. This is why the articles aren't hidden.

You need to use NOT EXISTS clause to check for this:

SELECT a.*
FROM tagmap at, articles a, tag t
WHERE at.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND a.id = at.articleID
AND NOT EXISTS
    (SELECT 1 from userExcludedArticles
     WHERE userID='1' AND a.id = articleID)

Now you don't need GROUP BY since there aren't multiple results per article and the articles are hidden if the userExcludedArticles contains a row with the article's id regardless of any other rows.

Also would be better to use JOIN syntax for the tables rather than multiple in the FROM section.

Upvotes: 3

Related Questions