Error: You have written a subquery that can return more than one field without using the EXIST reserved word

Question

I get the following error when I run the query.

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

This is my select statement:

sql = "SELECT (SELECT * FROM Data WHERE ProductDate = '" + yesterday + "' "+"AND EndTime > '" + endTime + "'), " + "(SELECT * FROM Data WHERE ProductDate = '" + todayDate + "' " + "AND EndTime = '" + endTime + "')";

The objective is to query both select statements into one query. How do I do that?

Please paramaterize this query rather than string concatenation — AntDC, Oct 19 '16 at 10:20

score 3 · Accepted Answer · edited May 23 '17 at 12:00

You could use UNION:

sql = "SELECT * FROM ("
    + "   SELECT * FROM Data WHERE ProductDate = '" + yesterday + "' AND EndTime > '" + endTime + "' "
    + "   UNION"
    + "   SELECT * FROM Data WHERE ProductDate = '" + todayDate + "' AND EndTime = '" + endTime + "' "
    + ")";

Or better, shorter and simpler with a WHERE on two OR-ed conditions:

sql = "SELECT * FROM Data "
    + "WHERE (ProductDate = '" + yesterday + "' AND EndTime > '" + endTime + "')"
    + "OR    (ProductDate = '" + todayDate + "' AND EndTime = '" + endTime + "')";

And as others have said: look into parameterized queries, because sooner or later one of the values may be injected with all kinds of horror. E.g. see this answer for that: https://stackoverflow.com/a/7505842/1220550.

One final thing: the usage of endTime seems strange combined with both dates as you did. But that is for you to know or decide, and not the topic of this question.

Error: You have written a subquery that can return more than one field without using the EXIST reserved word

1 Answers1