sql server - Restrict SQL subquery in SELECT -

i thought subquery within select statement restricted and/or join statements. therefore query returns error because there more 1 row in subquery.

select       dbo.countries.name,       dbo.countries.iso2,       (select dbo.countryfields.field         dbo.countryfields         dbo.countryfields.field = 'population') population       dbo.countryfields       inner join       dbo.countries on (dbo.countryfields.countries_id = dbo.countries.countries_id) 

how can restrict number of rows in subquery?

do need there inner join statement inside subquery? hoped subquery inherit normal select don't need manual restrictions.

the column "field" contains more "population" , show more rows in select statement subselects ... can't 1 column work. :-(

i think want this:

select       a.name,       a.iso2,       (select top 1 b.field dbo.countryfields b b.countries_id = a.countries_id , b.field = 'population') population,       (select top 1 b.field dbo.countryfields b b.countries_id = a.countries_id , b.field = 'capital') capital,       (select top 1 b.field dbo.countryfields b b.countries_id = a.countries_id , b.field = 'area') area       dbo.countries 

of course there ways optimize above query, it's tradeoff between readability , speed.

good luck!