CODEWITHSUNDEEP
sql-serversubquery
NealR
NealR

Reputation: 10669

Multi SELECT SQL statement

Writing my first nested SELECT statement and I'm hoping someone can help me out.

I need to grab the CompanyCode and AgentId from the UniqueAgentIdtoUniqueAgentId table. However, I only need the ones that show up in a query from another table. The common link between the rows will the the UniqueAgentId column (basically I need to grab the CompanyCode and AgentId from the rows that contain the same UniqueAgentId as come up in the nested query).

When I write this query out, I get the extremely helpful message

Incorrect syntax near ')'

Query:

SELECT 
    CompanyCode, AgentId
FROM 
    UniqueAgentIdToUniqueAgentId un
WHERE 
    un.UniqueAgentId = 
       (SELECT UniqueAgentId 
        FROM 
            (SELECT q.LastChangeDate, a.UniqueAgentId 
             FROM QueueUpdates q, AgentProductTraining a 
             WHERE a.LastChangeDate >= q.LastChangeDate)
       )

EDIT Thank you very much for the responses. Using every single query below, however I keep getting the same error message: Invalid object name 'UniqueAgentIdToUniqueAgentId'. This is odd to me because this is an actual table on the database that SQL Management Studio can see.

SECOND EDIT This turned out to simply be a spelling error. The new error is "The conversion of the varchar value '3030111101' overflowed an int column." Not quite sure what this means either...

FINAL EDIT I was attempting to compare a char with an it, which caused this error. Thanks again for all the help!

Upvotes: 4

Views: 108118

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

When you have a subquery in many databases, you need to give it an alias:

SELECT CompanyCode, AgentId
FROM UniqueAgentIdToUniqueAgentId un
WHERE un.UniqueAgentId = 
      (SELECT UniqueAgentId 
       FROM (SELECT q.LastChangeDate, a.UniqueAgentId 
             FROM QueueUpdates q, AgentProductTraining a 
             WHERE a.LastChangeDate >= q.LastChangeDate
            ) t
      )

However, your inner query might return multiple values, and you don't need two nested queries:

SELECT CompanyCode, AgentId
FROM UniqueAgentIdToUniqueAgentId un
WHERE un.UniqueAgentId in
      (SELECT UniqueAgentId 
       FROM QueueUpdates q, AgentProductTraining a 
       WHERE a.LastChangeDate >= q.LastChangeDate
      )

And, it is more proper to use JOIN syntax in this case:

SELECT CompanyCode, AgentId
FROM UniqueAgentIdToUniqueAgentId un
WHERE un.UniqueAgentId in
      (SELECT UniqueAgentId 
       FROM QueueUpdates q join
            AgentProductTraining a 
            on a.LastChangeDate >= q.LastChangeDate
      )

That should help.

Upvotes: 3

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

Your subquery may return more than 1 value. You must use IN.
You can also avoid the two subqueries and use only one.

SELECT CompanyCode, AgentId
FROM UniqueAgentIdToUniqueAgentId un
WHERE un.UniqueAgentId IN 
(
    SELECT a.UniqueAgentId 
    FROM QueueUpdates q, AgentProductTraining a 
    WHERE a.LastChangeDate >= q.LastChangeDate
)

Note that in this way you obtain a cartesian product of table aliases q and a. I don't know if this is what you want; maybe a join should be better.

Upvotes: 0

Robert
Robert

Reputation: 8767

You need to declare an alias, which is your actual reported issue, and likely use the IN clause instead of the = operator for multiple returns:

SELECT CompanyCode
    ,AgentId
FROM UniqueAgentIdToUniqueAgentId un
WHERE un.UniqueAgentId in (
        SELECT UniqueAgentId
        FROM (
            SELECT q.LastChangeDate
                ,a.UniqueAgentId
            FROM QueueUpdates q
                ,AgentProductTraining a
            WHERE a.LastChangeDate >= q.LastChangeDate
            ) a 
        )

Upvotes: 0

Tom
Tom

Reputation: 962

SELECT CompanyCode, AgentId
    FROM UniqueAgentIdToUniqueAgentId un
    WHERE un.UniqueAgentId = 
    (
        SELECT UniqueAgentId 
         FROM AgentProductTraining  where UniqueAgentId IN
           (
            SELECT q.LastChangeDate, a.UniqueAgentId 
            FROM QueueUpdates q, AgentProductTraining a 
            WHERE a.LastChangeDate >= q.LastChangeDate
            )
    )

Upvotes: 0

Related Questions