Syntax Error in SQL Statement

[OldBirdman]

The SQL statement:

SELECT tTitles.FKey FROM tTitles INNER JOIN tTitles ON (SELECT [ptr->tAAA] FROM tTitles WHERE (((Title) Like "*John*"))) = [ptr->tAAA]

produces the error

Syntax error. in query expression '(SELECT tTitles.Key FROM tTitles INNER JOIN tTitles ON (SELECT DISTINCT [ptr->tAAA] FROM tTitles WHERE (((Title) Like "*John*"))) = [ptr->tAAA]'.

Because I can run the query

SELECT DISTINCT [ptr->tAAA] FROM tTitles WHERE (((Title) Like "*John*"))

by itself, I assume that this isn't really a syntax error. What's going on?

[MikeTheBike]

@OldBirdman
Hi

As far as I am aware the first SQL is not a valid query. It is also unclear as to what you are trying to achive, for instance if this doesn't do what you want, what is wrong with it ??

SELECT tTitles.FKey FROM tTitles WHERE (((Title) Like "*John*"))

With an answer to this we may be able to help !

MTB

[ChipR]

I don't think you can't compare the results of a SELECT with a singular value, unless you SELECT TOP 1.

[OldBirdman]

It is also unclear as to what you are trying to achive, for instance if this doesn't do what you want, what is wrong with it ??

This does exactly what I want. Or it would if it didn't get a syntax error. What is wrong with it is it won't execute because of a syntax error. I know it will do as I want because I can remove the subquery and create a 2nd query, and save it. I can now replace the subquery with the name of the saved query, and it works as I want.

As far as I am aware the first SQL is not a valid query.

Why not?

[ajalwaysus]

Try This,

SELECT tTitles.FKey 
FROM tTitles
WHERE tTitles.[ptr->tAAA] IN (SELECT [ptr->tAAA] FROM tTitles WHERE Title Like "*John*")

Or Try,

SELECT tTitles.FKey 
FROM tTitles INNER JOIN (SELECT [ptr->tAAA] FROM tTitles WHERE Title Like "*John*") AS ALIAS1 on tTitles.[ptr->tAAA] = ALIAS1.[ptr->tAAA]

[OldBirdman]

ajalwaysus - Both of your code samples worked correctly in my project. I have been testing these, and some new ideas of my own.
The first code

SELECT tTitles.FKey  
FROM tTitles 
WHERE tTitles.[ptr->tAAA] IN 
    (SELECT [ptr->tAAA] 
    FROM tTitles 
    WHERE Title Like "*John*") 
 

slows down appreciably when the tables become large. I suspect that the "IN" feature doesn't like 10K+ items.

The second code

SELECT tTitles.FKey  
    FROM tTitles INNER JOIN (SELECT [ptr->tAAA] 
    FROM tTitles WHERE Title Like "*John*") AS ALIAS1 
    on tTitles.[ptr->tAAA] = ALIAS1.[ptr->tAAA] 
 

needs aliases, whereas using a saved query does not. This was the key I needed. Following up on this idea, I find that expanding this to use in my project required each item selected in the subQuery also must have an alias. So the subQuery becomes

SELECT tAAA.Field1 AS F1, tAAA.Field2 AS F2, ....... 
    FROM tAAA INNER JOIN ...... 
    WHERE ...
 

Then the query, as a general case, becomes

SELECT qry.F2, qry.F1, ... 
    FROM 
        (SELECT tAAA.Field1 AS F1, tAAA.Field2 AS F2, ....... 
            FROM tAAA INNER JOIN ...... 
            WHERE ...) as qry 
    WHERE ([conditions]) 
    ORDER BY Fn1, Fn2, ...

Mike - For, lets say, an Address Book, a record in tAddrBk would contain data about an individual or company. The name might be Robert Kennedy or Oakland Auto Repair, stored in another table, tNames. Also in tNames are alternate names, such as Bobby Kennedy or "Ken", the mechanic and owner of Oakland Auto. Searching for Bobby or Ken needs to find the alternate names, but display the primary names. tNames has a field Alt (Y/N). Therefore, your suggestion does not solve my problem.