Syntax Error in SQL Statement

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • OldBirdman
    Contributor
    • Mar 2007
    • 675

    Syntax Error in SQL Statement

    The SQL statement:
    Code:
    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
    Code:
    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
    Recognized Expert Contributor
    • Jun 2007
    • 640

    #2
    Originally posted by OldBirdman
    The SQL statement:
    Code:
    SELECT tTitles.FKey FROM tTitles INNER JOIN tTitles ON (SELECT [ptr->tAAA] FROM tTitles WHERE (((Title) Like "*John*"))) = [ptr->tAAA]
    produces the error
    Because I can run the query
    Code:
    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?
    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 ??
    Code:
    SELECT tTitles.FKey FROM tTitles WHERE (((Title) Like "*John*"))
    With an answer to this we may be able to help !

    MTB

    Comment

    • ChipR
      Recognized Expert Top Contributor
      • Jul 2008
      • 1289

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

      Comment

      • OldBirdman
        Contributor
        • Mar 2007
        • 675

        #4
        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?

        Comment

        • ajalwaysus
          Recognized Expert Contributor
          • Jul 2009
          • 266

          #5
          Try This,

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

          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]
          Last edited by ajalwaysus; Jul 21 '09, 03:38 PM. Reason: Added more code

          Comment

          • OldBirdman
            Contributor
            • Mar 2007
            • 675

            #6
            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
            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
            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
            Code:
            SELECT tAAA.Field1 AS F1, tAAA.Field2 AS F2, ....... 
                FROM tAAA INNER JOIN ...... 
                WHERE ...
            Then the query, as a general case, becomes
            Code:
            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.

            Comment

            Working...