CODEWITHSUNDEEP
sqlms-access
WhatEvil
WhatEvil

Reputation: 481

What's wrong with this SQL (Subquery using IN)?

I'm working with a junction table in Access (actually a self-join table which defines a parent>child relationship) and am running into trouble with some of the joins I need to make.

I currently have the following in one query:

(SELECT AccountID
FROM tblAccount
WHERE AccountTypeID IN (1, 2))
UNION (SELECT ChildAccountID 
FROM qryAccJunctionDetails
WHERE (ParentAccountTypeID IN (1, 2)));

Which works correctly: It provides a list of AccountIDs where the AccountTypeID is 1 or 2, or where that account is the child of another account with AccountTypeID = 1 or 2.

When I then try to use this as a subquery to find other field values related to this result like so:

SELECT AccountName 
FROM qryAccountNamesConcat
WHERE AccountID IN(
    (
        SELECT AccountID
        FROM tblAccount
        WHERE AccountTypeID IN (1, 2)
    )
    UNION (
        SELECT ChildAccountID 
        FROM qryAccJunctionDetails
        WHERE (ParentAccountTypeID IN (1, 2))
    )
);

I get the error: "Syntax error (missing operator) in query expression 'AccountID IN(...." which then continues to list right through to the end of the statement.

I've tried variations on it and can't seem to get it to work. It may be that I'm misunderstanding entirely how to put subqueries together in this way.

Edit: Now fixed missing parenthesis at the end of (ParentAccountTypeID IN (1, 2)) but still getting the same error.

Upvotes: 0

Views: 104

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Do this with two separate comparisons:

SELECT AccountName 
FROM qryAccountNamesConcat
WHERE AccountID IN (SELECT AccountID
                    FROM tblAccount
                    WHERE AccountTypeID IN (1, 2)
                   ) OR
      AccountID IN (SELECT ChildAccountID 
                    FROM qryAccJunctionDetails
                    WHERE ParentAccountTypeID IN (1, 2)
                   );

This may also have better performance.

Upvotes: 1

Alex
Alex

Reputation: 371

close the parenthesis, try this

SELECT AccountName 
FROM qryAccountNamesConcat
WHERE AccountID IN(
    (
        SELECT AccountID
        FROM tblAccount
        WHERE AccountTypeID IN (1, 2)
    )
    UNION (
        SELECT ChildAccountID 
        FROM qryAccJunctionDetails
        WHERE (ParentAccountTypeID IN (1, 2)) <-- here
    )
);

Upvotes: 1

Related Questions