Searching for articles by tag and other criteria

Here is an expansion of the problem from the last post. Now I have many articles, each has a title, an author, a list of tags, and other parameters. Let's say for simplicity that all the data is in one table - Articles, with the fields: id, title, author, and the tags are in a separate table called Tags with the fields ArticleID and Name.
Now I am looking for a single SQL command to search for articles with some text in their title, some text in their author, and some text in one of their tags.
Since there could be more then one tag for each article, it cannot be done by a simple query on the articles table.
To do that, I need to use subquery in the WHERE clause.
Here is the solution:


SELECT Atricles.ID, Articles.Title, Articles.Author
FROM Articles
WHERE Article.Title LIKE '%someText1%'
AND Article.Author LIKE '%someText2%'
AND (0 < (SELECT COUNT(*) FROM Tags
WHERE Tags.ArticleID = Articles.ID
AND Tags.NAME LIKE '%someText3%')

Find an article with a list of tags

Let's say I have a web site with articles, and each article can have one or more tags, just like the blog posts in this web site (where they are called "labels").
I save all of the article's tags in a table, called ArticleTags, that have two columns: ArticleId and TagId.
Now let's say I have a list of tags - ASP.NET, SQL and WEB, for example, with IDs 1,2,3.
I want to find all of the articles that have all 3 tags.
If I try the simple query

SELECT ArticleID FROM ArticleTags WHERE TagId in (1,2,3)

I will get all the articles with either tag 1 or tag 2 or tag 3 - not the articles with all three of them.

When I looked for solutions on the web, I was surprised to find that many implemented this by saving all the tags as one long text field of the articles = "ASP.NET,SQL,WEB" - and used "LIKE" in the query. It is inefficient, because of the use of "like", and because the search is on the tag text itself and not on the tag ID, and also limits the number of tags to the capacity of one field, and I also want to save the text in a separate table, for localizations and other uses.

I finally got the following solution that gives me exactly what I was looking for:

SELECT ArticleID
FROM ArticleTags
WHERE (TagId IN (1, 2, 3))
GROUP BY ArticleID
HAVING (COUNT(*) = 3)

I am using the GROUP option to group all the same ArticleID together, and then count them. If their count is exactly the number of Tag IDs I am looking for, it means that this article has all the tags in my list. This condition has to be in the HAVING part of the query and not in the WHERE clause because it uses an aggregation field.

Finding records in one table that are NOT referenced in another table

Sometimes we have one table records referencing records in another table. What if we want to find all the records in one table that are not referenced by ANY record in the other table?
For example, say we have one record of people's names: a table called PEOPLE with two fields: ID and NAME. The other table holds all nicknames of the people in the first table. It has three fields: ID, PeopleID, and Nickname. Values for these table could be:

PEOPLE
ID Name
--------
1 Michael

NICKNAMES
ID PeopleID Nickname
------------------------
1 1 Micky
2 1 Mick

Now let's say we want to find all people who DON'T have any nick name.
Here is how we do that:

SELECT PEOPLE.ID, PEOPLE.Name
FROM PPEOPLE LEFT OUTER JOIN NICKNAMES
ON PEOPLE.ID = NICKNAMES.PeopleID
WHERE NICKNAME.PeopleID IS NULL

First we are using outer join to make sure we get these records, even though the matching records in the other table to not exist.

Then we use the IS NULL condition. Note that '= NULL' will not work (I know, I tried it first...)

Now we have all the people with not even one nickname.