sql server - Subquery inside a view is not working corectly -

hey creating view used in fastreport(reportingsoftware) , have sub-query in form of scalar-valued function inside pull full address of property, though bringing error subquery returned more 1 value , im not sure why, have used similar in past cant seem figure out issue.

here view code

select  top (100) percent dbo.propertymaster.propertyid,          dbo.lookup_propertymanager.description, dbo.lookup_propertymanager.email,          dbo.getfulladdress(dbo.propertymaster.propertyid) fulladdress,          dbo.tenants.tenantid, dbo.tenants.leaseid,          dbo.tenants.tenantforename + ' ' + dbo.tenants.tenantsurname fullname,          dbo.propertymaster.spmreference,          convert(varchar, dbo.propertylease.startdate,101) startdate,          convert(varchar, dbo.propertylease.datesigned, 101) datesigned  dbo.propertylease        right outer join dbo.propertymaster               on dbo.propertylease.propertyid = dbo.propertymaster.propertyid        left outer join dbo.tenants               on dbo.propertymaster.propertyid = dbo.tenants.propertyid        left outer join dbo.lookup_propertymanager      on dbo.propertymaster.propertymanagerid = dbo.lookup_propertymanager.propertymanagerid order dbo.propertymaster.propertyid 

and here scalar-valued function

alter function [dbo].[getfulladdress]      -- add parameters function here     (@propertyid integer ) returns varchar(250)  begin declare @addressline varchar(40) declare @fulladdress varchar(250) set @fulladdress = (select ltrim(isnull(tenantforename + ' ', ' ') + tenantsurname) fullname tenants  propertyid = @propertyid) + char(10)   set @addressline = (select isnull(addressline1, '') propertymaster propertyid = @propertyid) set @fulladdress = @fulladdress + @addressline + char(10)         set @addressline = (select  isnull(addressline2, '') propertymaster propertyid = @propertyid) if @addressline <> ''     begin         set @fulladdress = @fulladdress + @addressline + char(10)     end         set @addressline = (select  isnull(addressline3, '') propertymaster propertyid = @propertyid) if @addressline <> ''     begin         set @fulladdress = @fulladdress + @addressline + char(10)     end set @addressline = (select isnull(town, '' ) propertymaster propertyid = @propertyid) if @addressline <> ''     begin         set @fulladdress = @fulladdress + @addressline + char(10)     end set @addressline = (select isnull(postcode, '') propertymaster propertyid = @propertyid) set @fulladdress = @fulladdress + @addressline  return @fulladdress end 

the problem line

set @fulladdress = (select ltrim(isnull(tenantforename + ' ', ' ') + tenantsurname) fullname tenants  propertyid = @propertyid) + char(10) 

you can change first tenant adding top 1

set @fulladdress = (select top 1 ltrim(isnull(tenantforename + ' ', ' ') + tenantsurname) fullname tenants  propertyid = @propertyid) + char(10) 

or can concatenate of tenants together.

select @fulladdress = coalesce(@fulladdress, '')         + ltrim(isnull(tenantforename + ' ', ' ') + tenantsurname) fullname    tenants    propertyid = @propertyid) + char(10) 

p.s. if issue. view returning duplicate rows since left joining tenants table also. if need row each tenant in view, you'll want pass in tenantid function , add clause query tenant name.