T-SQL - Subquery returned more than 1 value

Question

Have 2 tables: users and offers.

I need to extract 3 offers for each user.

I wrote this command line:

SELECT TOP 10  
    sellerID, 
    Country, 
    (SELECT TOP 3 ItemID 
     FROM Items i 
     JOIN Sellers s ON s.sellerID = i.UserID 
     ORDER BY CreatedDate)
FROM 
    Sellers 
ORDER BY
    Country desc

Not sure if really returns what I'm looking for, but I get this error:

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

Can suggest a solution or a workaround?

Answer 1

CROSS APPLY is what you need. Please see query example:

SELECT TOP (10) S.SellerID, S.Country, I.ItemID
FROM dbo.Sellers AS S
CROSS APPLY (
    SELECT TOP (3) I.ItemID
    FROM dbo.Items AS I
    WHERE S.SellerID = I.UserID
    ORDER BY I.CreatedDate
    ) AS I
ORDER BY S.Country DESC;

This query will bring you back TOP (10) results. If you want to get TOP (10) users and 3 items for each user (This means up to 30 records), use this query instead:

SELECT S.SellerID, S.Country, I.ItemID
FROM (
    SELECT TOP (10) S.SellerID, S.Country
    FROM dbo.Sellers AS S
    ORDER BY S.Country DESC
    ) AS S
CROSS APPLY (
SELECT TOP (3) I.ItemID
FROM dbo.Items AS I
WHERE S.SellerID = I.UserID
ORDER BY I.CreatedDate
) AS I;

One of it's uses is exactly what you're asking. Returning TOP (n) subitems. More real life examples can be found here: Real life example, when to use OUTER / CROSS APPLY in SQL

Answer 2

You have the subquery in the select clause. It must return 1 row there. Try joining to it instead-

SELECT 
TOP 10  sellerID, 
Country, items.ItemID
from Sellers 
left join
(
   SELECT TOP 3 UserID, ItemID 
   from Items i 
   join Sellers s  
   on s.sellerID=i.UserID 
   order by CreatedDate
) items
on sellers.UserID = items.UserID
order by Country desc

Answer 3

The immediate problem is that you have asked a select to output rows each containing a value for sellerId, Country and three values for ItemID.

The subquery is also not correlated with the outer query, i.e. you have a second reference to the Sellers table, but have done nothing to match up values with the outer query.

Assuming that you actually want the top 3 items for each of up to 10 countries, this should be close:

-- Sample data.
declare @Sellers as Table ( SellerId Int, Country VarChar(16) );
insert into @Sellers ( SellerId, Country ) values
  ( 1, 'Canada' ), ( 2, 'Italy' ), ( 3, 'Elbonia' ), ( 4, 'Maldives' ), ( 5, 'Fiji' ),
  ( 6, 'Ecuador' ), ( 7, 'Chile' ), ( 8, 'Mexico' ), ( 9, 'Palau' ), ( 10, 'Yap' ),
  ( 11, 'Saba' );
declare @Items as Table ( ItemId Int, UserId Int, CreatedDate Date );
insert into @Items ( ItemId, UserId, CreatedDate ) values
  ( 1, 3, '20150308' ), ( 2, 3, '20150619' ), ( 3, 3, '20120908' ), ( 4, 3, '20140228' ),
  ( 2, 9, '20150308' ), ( 3, 9, '20150619' ), ( 4, 9, '20120908' ), ( 5, 9, '20140228' ),
  ( 3, 6, '20150308' ), ( 4, 6, '20150619' ), ( 4, 6, '20120908' ), ( 6, 6, '20140228' );

select * from @Sellers;
select * from @Items;

-- Show the intermediate results.
with AllSellersAndItems as
  ( select S.SellerId, S.Country, I.ItemId,
    Row_Number() over ( partition by S.SellerId order by I.CreatedDate ) as ItemSequence,
    Dense_Rank() over ( order by Country ) as SellerSequence
    from @Sellers as S inner join
      @Items as I on I.UserId = S.SellerId )
  select * from AllSellersAndItems;

-- The real query.
with AllSellersAndItems as
  ( select S.SellerId, S.Country, I.ItemId,
    Row_Number() over ( partition by S.SellerId order by I.CreatedDate ) as ItemSequence,
    Dense_Rank() over ( order by Country ) as SellerSequence
    from @Sellers as S inner join
      @Items as I on I.UserId = S.SellerId )
  select SellerId, Country, ItemId, SellerSequence, ItemSequence
    from AllSellersAndItems
    where SellerSequence <= 10 and ItemSequence <= 3
    order by Country desc

Answer 4

this query work for you :

    SELECT  TOP 10  sellerID,  Country, items.ItemID
    from Sellers 
    left join Items On Items.UserID = Sellers.sellerID
                   And Items.ItemID in ( Select Top 3 I_1.ItemID From Items As I_1
                                         Where I_1.UserID = Sellers.sellerID
                                         Order by CreatedDate )
   ORDER BY Country desc