0

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!

thatguy
  • 57
  • 9
  • Any reason why you are not using a join on `Sales` and `Products` in the main query and having the `min([date to use])` as a sub query? – Verma Jul 11 '16 at 10:21
  • Not particularly. This way just seemed to work. I'll work on creating a join on to the subquery. Suggestions are welcome! Cheers – thatguy Jul 11 '16 at 10:49

1 Answers1

1

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]);
Verma
  • 956
  • 6
  • 21
  • This seems to do just as I needed. I have never created joins using this method, I have always had to write out 'INNER JOIN....' etc. Is there any difference between the join you have shown above, compared to a traditional one you would do in table view? Many thanks – thatguy Jul 11 '16 at 11:10
  • no real difference except readability. See... http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – Verma Jul 11 '16 at 11:14
  • One question, is there a way of speeding this query up? Using the join method has impacted the performance of the query quite significantly. All necessary columns have been indexed already, but the join method takes about 5-10x longer – thatguy Jul 11 '16 at 13:20
  • You might want to see the execution plan to ensure the indexes are actually being used. I guessing you already have indexes on Products.Prod1 and Sales.Prod, but you may not have indexes on Sales.Date and Products.[Date to use]. Adding these may help. – Verma Jul 12 '16 at 09:15