How do I use my alias in the where clause?

Question

I'm trying to search multiple columns of text and memos for certain phrases and blacklist phrases I don't want to see.

Assume the following table

stories: 
id, title, author, publisher, content

Ex. I want to find all stories that mention (in any field) 'apples' but blacklist 'applesauce'.

SELECT stories.id, [stories.title] & " " & [stories.author] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"));

How do I use my alias in the where clause? I can't find any documentation on the subject:

1) Is this approach possible?
2) Wouldn't the alternative mean that I'd be performing multiple string concatenations on every row iteration?

Answer 1

I can't use my alias in the where clause.

1. Is this approach possible?

Sure, put it in a subquery.

SELECT *
FROM
(
SELECT stories.id, [stories.title] & " " & [stories.author] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
) AS SUBQ
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"));

2. Wouldn't the alternative mean that I'd be performing multiple string concatenations on every row iteration?

Yes that is right, the alternative is to repeat the expression. I won't bore you with the code for this alternative.

For your particular query, you can also use this

SELECT stories.id, [stories.title] & " " & [stories.author] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
WHERE ([stories.title] Like "*apples*" OR [stories.author] Like "*apples*" 
  OR [stories.publisher] Like "*apples*" OR [stories.memo] Like "*apples*")
AND NOT ([stories.title] Like "*applesauce*" OR [stories.author] Like "*applesauce*"
  OR [stories.publisher] Like "*applesauce*" OR [stories.memo] Like "*applesauce*")

Answer 2

The only problem is that, no matter what I try to do, I can't use my alias in the where clause. I can't find any documentation on the subject

Yes, the documentation for Access/Jet/ACE 'SQL' language is severely lacking and the little that is available has shocking errors.

Here's some documentation about SQL generally:

"Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL", ch12, pp235-237:

Here is how a SELECT works in SQL... Start in the FROM clause... Go to the WHERE clause... Go to the optional GROUP BY clause... Go to the optional HAVING clause... Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can also give names to expressions in the SELECT list. These new names come into existence all at once, but after the WHERE clause, GROUP BY clause and HAVING clause have been executed; you cannot use them in the SELECT list or the WHERE clause for that reason.

I think this explains why you cannot use an as clause ("column alias") in the WHERE clause in Access (Jet, ACE, whatever).

That said, note that Access is non-compliant with SQL in that it allows you to use an as clause in the SELECT clause in left-to-right direction e.g. this is legal in Access SQL (but illegal in Standard SQL):

SELECT 2 AS a, 2 AS b, a + b AS c
  FROM tblMyTable

Answer 3

Use Subqueries:

Select id,allMyText 
from
(SELECT stories.id, 
[stories.title] & " " & [stories.author] & " " 
& [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories ) as w
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"))