Hi all I found this site after reading through an old post where Joseph Craig gave the answer I was looking for. Here is what I am asking:
I am attempting to make a report that shows only the user's Username, Firstname, Lastname, Email, Numerical, Street, Telephone, Committee and Volunteer Profile field values (from statement # 1 which shows everything fine but includes unauthorized members) who's accounts are authorized (statement #2 shows the accounts that are true from the Authorised column) so, I have two different statements That I have not been able to figure out how to join. Both statements work by them self, but I can not seem to join them together proerly. they are as follows:

Statements:

Statment#1

SELECT
u.UserName,
u.FirstName,
u.LastName,
u.Email,
Numerical.PropertyValue AS Numerical,
Street.PropertyValue AS Street,
Telephone.PropertyValue As Telephone,
Committee.PropertyValue AS Committee,
Volunteer.PropertyValue as Volunteer
FROM
dbo.udf_UserProfileField('unit') AS Numerical left OUTER JOIN dbo.vw_Users AS U on Numerical.PortalId = U.PortalId AND Numerical.UserID = U.UserId and Numerical.PropertyValue >= ' ' join
dbo.udf_UserProfileField('street') AS Street ON U.UserId = Street.UserID AND U.PortalId = Street.PortalId and Street.PropertyValue >= ' ' join
dbo.udf_UserProfileField('Telephone') AS Telephone ON U.UserId = Telephone.UserID AND U.PortalId = Telephone.PortalId and Telephone.PropertyValue >= ' ' join
dbo.udf_UserProfileField('Committee') AS Committee ON U.UserId = Committee.UserID AND U.PortalId = Committee.PortalId and Committee.PropertyValue >= ' ' Join
dbo.udf_UserProfileField('Volunteer') AS Volunteer on U.UserId = Volunteer.UserID AND U.PortalId = Volunteer.PortalId
and volunteer.PropertyValue >= ' '

Statement #2
SELECT Authorised from
UserPortals where Authorised='true'



This may seem to be juvenile stuff to a lot of you, but I haven't really messed with web design stuff since HTML. I would appreciate any help/advice that you can give to nudge me into the right direction. I do not even need to show the second statement. Just the fields from the first statement where users are approved.
we are using:
The DotNetNuke application you are running DotNetNuke Product:
DotNetNuke Community Edition
The DotNetNuke application version you are running DotNetNuke Version:
05.06.02 (144)

and the way I figured out the long statement for user defined fields was by running this script:

CREATE FUNCTION [dbo].[udf_UserProfileField]
(
@PropertyName NVARCHAR(50)
)
RETURNS @ProfileFieldTable TABLE
(
PortalId INT,
UserID INT,
PropertyName NVARCHAR(50),
PropertyValue NVARCHAR(3750)
)
AS BEGIN
INSERT INTO @ProfileFieldTable
SELECT PPD.PortalID,
UP.UserID,
PPD.PropertyName,
UP.PropertyValue
FROM dbo.Users AS U
INNER JOIN dbo.UserProfile AS UP ON U.UserID = UP.UserID
INNER JOIN dbo.ProfilePropertyDefinition AS PPD ON UP.PropertyDefinitionID = PPD.PropertyDefinitionID
WHERE ( PPD.PropertyName = @PropertyName )

RETURN
END

Scot,

I will get back to you on this. If you don't hear from me withing a week, please remind me.

This is actually complicated stuff, not simple.

Joseph, Just the man I was looking for. Let me start off by saying Thank You! I have found most of the answers to my questions by reading through the help you have given others. I hope you didn't start working on figuring it out yet. I had trouble posting this question and figured it did not post. I figured out what I was looking for after many hours of testing. I took over the programing for a site after it was mostly abandoned. When I started I only knew how to write HTML script that I haven't used for 10+ years, so you have been an inspiration and big help without even knowing it. Below is the answer(s) I was looking for in a few different forms, hopefully it will help others.
Thank you once again,
Scott Price

This version returns all the authorized members without showing the authorized category:
SELECT
Username,
FirstName,
LastName,
Email,
Numerical.PropertyValue AS Numerical,
Street.propertyValue as Street,
Telephone.PropertyValue As Telephone,
Committee.PropertyValue AS Committee
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.UserPortals on UserPortals.Userid = Users.Userid where UserPortals.Authorised='True'

This version shows the same but with the authorized category showing true and false:
SELECT
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 >= ' '