By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

Syntax Error in SQL Statement

100+
P: 675
The SQL statement:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 20 '09 #1

✓ answered by ajalwaysus

Try This,

Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey 
  2. FROM tTitles
  3. WHERE tTitles.[ptr->tAAA] IN (SELECT [ptr->tAAA] FROM tTitles WHERE Title Like "*John*")
Or Try,

Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey 
  2. FROM tTitles INNER JOIN (SELECT [ptr->tAAA] FROM tTitles WHERE Title Like "*John*") AS ALIAS1 on tTitles.[ptr->tAAA] = ALIAS1.[ptr->tAAA]

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 635
@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 ??
Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey FROM tTitles WHERE (((Title) Like "*John*"))
With an answer to this we may be able to help !

MTB
Jul 21 '09 #2

Expert 100+
P: 1,287
I don't think you can't compare the results of a SELECT with a singular value, unless you SELECT TOP 1.
Jul 21 '09 #3

100+
P: 675
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?
Jul 21 '09 #4

Expert 100+
P: 266
Try This,

Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey 
  2. FROM tTitles
  3. WHERE tTitles.[ptr->tAAA] IN (SELECT [ptr->tAAA] FROM tTitles WHERE Title Like "*John*")
Or Try,

Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey 
  2. FROM tTitles INNER JOIN (SELECT [ptr->tAAA] FROM tTitles WHERE Title Like "*John*") AS ALIAS1 on tTitles.[ptr->tAAA] = ALIAS1.[ptr->tAAA]
Jul 21 '09 #5

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey  
  2. FROM tTitles 
  3. WHERE tTitles.[ptr->tAAA] IN 
  4.     (SELECT [ptr->tAAA] 
  5.     FROM tTitles 
  6.     WHERE Title Like "*John*") 
  7.  
slows down appreciably when the tables become large. I suspect that the "IN" feature doesn't like 10K+ items.

The second code
Expand|Select|Wrap|Line Numbers
  1. SELECT tTitles.FKey  
  2.     FROM tTitles INNER JOIN (SELECT [ptr->tAAA] 
  3.     FROM tTitles WHERE Title Like "*John*") AS ALIAS1 
  4.     on tTitles.[ptr->tAAA] = ALIAS1.[ptr->tAAA] 
  5.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tAAA.Field1 AS F1, tAAA.Field2 AS F2, ....... 
  2.     FROM tAAA INNER JOIN ...... 
  3.     WHERE ...
  4.  
Then the query, as a general case, becomes
Expand|Select|Wrap|Line Numbers
  1. SELECT qry.F2, qry.F1, ... 
  2.     FROM 
  3.         (SELECT tAAA.Field1 AS F1, tAAA.Field2 AS F2, ....... 
  4.             FROM tAAA INNER JOIN ...... 
  5.             WHERE ...) as qry 
  6.     WHERE ([conditions]) 
  7.     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.
Jul 23 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.