Filipe Hemsworth Filipe Hemsworth - 4 months ago 11
SQL Question

Adding additional column to subquery gives error requesting to use 'EXISTS' for subqueries with more than one field

As the title describes, I have a query which works perfectly as it is, however I would like to bring in an additional column from the table in the subquery, which is currently giving me the error below:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

Is it possible to bring in more than one column in a subquery? Or would this be better done with a join? My current code is below:

SELECT S.Prod, S.Date, (SELECT Price,
FROM [Products] P
WHERE P.[Prod1] = S.[Prod]
AND P.[Date to use] = (SELECT MIN(P2.[Date to use])
FROM [Products] P2
WHERE P2.[Prod1] = P.[Prod1]
AND P2.[Date to use] > S.[Date])) AS Price
FROM [Sales] AS S;


I would like to bring in a field called 'Cost', below is my incorrect code which is returning the error:

SELECT S.Prod, S.Date, (SELECT Price, Cost
FROM [Products] P
WHERE P.[Prod1] = S.[Prod]
AND P.[Date to use] = (SELECT MIN(P2.[Date to use])
FROM [Products] P2
WHERE P2.[Prod1] = P.[Prod1]
AND P2.[Date to use] > S.[Date])) AS Price
FROM [Sales] AS S;


Please could someone show me how I would go about doing this? Cheers!

Answer

You could start with this...

SELECT S.Prod, S.Date, P.Price, P.Cost
FROM [Sales] S, [Products] P
WHERE P.[Prod1] = S.[Prod]
AND P.[Date to use] = (SELECT MIN(P2.[Date to use])
                         FROM   [Products] P2
                         WHERE  P2.[Prod1] = S.[Prod]
                         AND    P2.[Date to use] > S.[Date]);