Msg 116, Level 16, State 1, Line 2 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Question

I got this error message when I tried to run this code

select *
from Folio_Guest
where FolioID = (
    select *
    from Folio
    where ArrivalDate between '20190101' and '20210901'
)

What about the error don't you understand? You are selecting *, you only want one column. — Dale K, Sep 04 '21 at 00:59
select * from Folio_Guest where FolioID = (select FolioID from folio where ArrivalDate between '20190101' and '20210901') — Omar Elfky, Sep 04 '21 at 01:32
You're still selecting * - how can * (multiple columns) ever match a single value? Select a single column... — Dale K, Sep 04 '21 at 01:33
https://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not — Dale K, Sep 04 '21 at 01:56
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Sep 08 '21 at 09:21

score 0 · Answer 1 · answered Sep 04 '21 at 03:25

The subquery needs to return at most one row and only one column. Perhaps you intend something like this:

select fg.*
from Folio_Guest fg
where fg.FolioID in (select f.folioID
                     from Folio f
                     where ArrivalDate between '20190101' and '20210901'
                    );

The important changes are:

The subquery returns just one column, the one that should match to folioID.
The comparison uses in rather than = so it can match multiple rows, if appropriate.

Msg 116, Level 16, State 1, Line 2 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

1 Answers1