CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Diver Dan
Diver Dan

Reputation: 9963

Create a sql variable and query other tables

I'm trying to query a table and get the results into a variable. With the variable results do another select query using the variable details as the filter.

So far:

DECLARE @storeIds int    
SET @storeIds = (SELECT StoreID FROM Store WHERE ParentStoreID=9)

--print @storeIds

SELECT c.FirstName, c.LastName, c.CustomerId, r.StoreID
FROM Consumer AS c
    INNER JOIN Purchases AS r ON c.CustomerId= r.CustomerId
WHERE r.StoreID = @storeIds
   -- (r.StoreID = 9) OR
   -- (r.StoreID = 10) OR
   -- (r.StoreID = 11)
GROUP BY c.FirstName, c.LastName, c.CustomerId, r.StoreID
ORDER BY c.FirstName

I get an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 1

Views: 94

Answers (4)

Elias Hossain
Elias Hossain

Reputation: 4469

Can be done in different ways:

  1. Using sub query

    SELECT c.FirstName, c.LastName, c.CustomerId, r.StoreID
FROM Consumer AS c
   INNER JOIN Purchases AS r ON c.CustomerId= r.CustomerId
WHERE r.StoreID = (SELECT StoreID FROM Store WHERE ParentStoreID=9)
GROUP BY c.FirstName, c.LastName, c.CustomerId, r.StoreID
ORDER BY c.FirstName

  2. Using Join Operation

    SELECT c.FirstName, c.LastName, c.CustomerId, r.StoreID
FROM Consumer AS c
    INNER JOIN Purchases AS r ON c.CustomerId= r.CustomerId 
    INNER JOIN (SELECT StoreID FROM Store WHERE ParentStoreID=9) AS s(StoreID)
        ON r.StoreID = s.StoreID
GROUP BY c.FirstName, c.LastName, c.CustomerId, r.StoreID
ORDER BY c.FirstName

Upvotes: 0

TimothyAWiseman
TimothyAWiseman

Reputation: 14863

Barry and Adam Wenger have the best approach, but to directly address the error, you can ensure that you are getting exactly one result while setting the variable by using the top 1 modifier. Like:

DECLARE @storeIds int     
SET @storeIds = (SELECT top 1 StoreID FROM Store WHERE ParentStoreID=9)

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7761

Unless you particularly want the @StoreIds variable elsewhere you could just amend your WHERE clause to:

WHERE r.StoreID IN (SELECT StoreID FROM Store WHERE ParentStoreID = 9)

Upvotes: 2

Adam Wenger
Adam Wenger

Reputation: 17540

Your issue is that multiple stores have the same ParentStoreID, so when you query, you are trying to put multiple values into your INT variable.

You could look at trying:

SELECT c.FirstName, c.LastName, c.CustomerId, p.StoreID
FROM Consumer AS c
INNER JOIN Purchases AS p ON c.CustomerId = p.CustomerId
INNER JOIN Store AS s ON p.StoreId = s.StoreId
WHERE s.ParentStoreID = 9
GROUP BY c.FirstName, c.LastName, c.CustomerId, r.StoreID
ORDER BY c.FirstName

This query should give you all the purchases you want, from all of the stores with ParentStoreId = 9.

Information about JOINS might help you as well.

Upvotes: 1

Related Questions