3

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?

Community
  • 1
  • 1
Raymond Berg
  • 860
  • 5
  • 17

3 Answers3

5

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*")
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • The subquery approach is really not going to be that helpful, seems to me -- it's over-engineering a solution to the problem. BTW, if people would simply use the Access query builder for this kind of thing, they'd avoid this kind of problem (it will produce correct Jet/ACE-compatible SQL). – David-W-Fenton Feb 06 '11 at 00:20
  • @David-W-Fenton: the OP has accepted this answer, so it's back to you to define what you mean by "is really not going to be that helpful". Also, I'd like to see your "solution" so that we can assess it for "over-engineering"-ness. – onedaywhen Feb 07 '11 at 09:39
  • I would go with your second solution. It's only the first one that I don't see as useful. – David-W-Fenton Feb 08 '11 at 05:00
4

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
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • While not "the answer" you've really helped me understand the problem in much more depth! Thanks so much for the thoughtful response! – Raymond Berg Feb 04 '11 at 13:09
  • If Access SQL is not "SQL at all", then SQL must be a meaningless moniker, since there is no other SQL implementation that conforms to EVERY detail of the ANSI standard. Even if there is a perfect implementation, it is likely only for a particular version, and different versions allow different implementations. So simply referring to "SQL" is meaningless unless it includes the full standard version reference. So we could thereby "prove" that all products which claim to support SQL really do not. – C Perkins Apr 06 '19 at 20:59
  • @CPerkins: The "not SQL" remark was just me being flippant. I've removed it. – onedaywhen Apr 10 '19 at 13:00
  • @CPerkins: FWIW I think a good benchmark for "Is SQL" is entry-level SQL-92. Access SQL does not meet this criteria, whereas all the other popular products do (SQL Server, DB2, Oracle, Postgres, mySQL, etc). No SQL product is, and no SQL product will ever be, full SQL-92 compliant because it contains features that nobody wants (`UNION JOIN') or that are not practical to implement (`CREATE ASSERTION`). But then I guess you were similarly being flippant. – onedaywhen Apr 10 '19 at 13:09
  • @CPerkins: to be fair, I don't think Access claims to support SQL. My point was: if you can't consult/trust the Access documentation because it doesn't exist/contains errors and you can't consult standard SQL texts because it doesn't apply to Access's version of SQL, is it a product that people should be using for business-critical applications? – onedaywhen Apr 10 '19 at 13:13
1

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*"))
Crimsonland
  • 2,194
  • 3
  • 24
  • 42