Обсуждение: sub selects
I hope this is not too confusing..... I left my column names and table names as is but you should get the idea...
I am doing the following:
select usf_fk from am_spots where ams_pk in ((select min(ams_pk) from am_spots where am_fk>135),(select max(ams_pk) from am_spots where am_fk>135);
It works fine but I originally tried this:
select usf_fk from am_spots where ams_pk in (select min(ams_pk), max(ams_pk) from am_spots where am_fk>135);
I received an error saying that my subselect has too many fields. Did I use the wrong syntax or is it just a postgres rule that you can't pull more than one field back in a sub select?
Also, I need to now embed this entire select statement into another select statement such as:
select spot_identifier from al_spots where als_pk in (SELECT STATMENT ABOVE)
Can I do this? Is there any easier way that I cannot see? Any suggestions would be appreciated.
Thanks
Jodi
Jodi Kanter <jkanter@virginia.edu> writes:
> select usf_fk from am_spots where ams_pk in (select min(ams_pk), max(ams_pk=
> ) from am_spots where am_fk>135);
> I received an error saying that my subselect has too many fields. Did I use=
> the wrong syntax or is it just a postgres rule that you can't pull more th=
> an one field back in a sub select?
It's an SQL92 rule: if you are doing "foo IN (subselect)" then the
subselect must return one column to compare to foo. You are confusing
this case with "foo IN (scalar expression, scalar expression, ...)"
which is actually quite a different construct.
regards, tom lane