I figured it out finally!!!! I am posting this as a reference for others.
I used this statement to figure it out:
Select username, Firstname, lastname, UserPortals.Authorised
from
users
Right Join dbo.UserPortals on UserPortals.Userid = Users.Userid where Authorised='true'
I then tweaked my previous statement to this and it works:
SELECT
Users.UserId,
Username,
FirstName,
LastName,
Email,
Numerical.PropertyValue AS Numerical,
Street.propertyValue as Street,
Telephone.PropertyValue As Telephone,
Committee.PropertyValue AS Committee,
Volunteer.PropertyValue as Volunteer,
UserPortals.Authorised
FROM
Users
Right Join dbo.udf_UserProfileField('Unit') as Numerical on Numerical.UserID = Users.UserId and Numerical.PropertyValue >= ' '
Right Join dbo.udf_UserProfileField('Street') as Street on Street.UserId = Users.UserID and Street.PropertyValue >= ' '
Right Join dbo.udf_UserProfileField('Telephone') as Telephone on Telephone.UserId = Users.UserID AND Telephone.PropertyValue >= ' '
Right Join dbo.udf_UserProfileField('Committee') as Committee on Committee.UserID = Users.UserID and Committee.PropertyValue >= ' '
Right Join dbo.udf_UserProfileField('Volunteer') as Volunteer on Volunteer.UserID = Users.UserID and volunteer.PropertyValue >= ' '
Right Join dbo.UserPortals on UserPortals.Userid = Users.Userid where Authorised='true'
I want to thank Mark for helping and everyone who took the time to read my question!