Help with table join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with table join

Author

Topic

mbooher
Starting Member

3 Posts

Posted - 2014-12-30 : 11:58:48

I am a newbie to SQL and I am trying to create a stored procedure to use with Crystal Reports.

I don't understand why I am having trouble joining tables to the Member table. These table joins have worked in the past.

Create Proc rpmb_FamilyPortraitDirectoryP1Test

(
@ChurchId int,
@StartFamilyName varchar(50),
@EndFamilyName varchar(50),
@MemberTypeId varchar(max) = Null,
@MemberStatusId varchar(max) = Null
)

AS
Begin

Select Family.FamilyId,
Family.FamilyName,
Family.SalutationTypeValue,
FamilyPortrait.FamilyPortraitPath,
FamilyPortrait.IsCurrent,
MemberType.Description AS MemTypeDESC,
MemberStatus.Description AS MemStatusDesc,
Left(Member.FMembers,Len(Member.FMembers)-1) As "FMembers"
From
(
Select distinct ST2.FamilyId,
(
Select ST1.Preferred + ', ' AS [text()]
From Member ST1
Where ST1.FamilyId = ST2.FamilyId
ORDER BY ST1.FamilyId
For XML PATH ('')
) FMembers
From Member ST2
)
Member
Left Outer Join Family
On Member.FamilyId = Family.FamilyId and Family.IsDeleted = 0
Left Outer Join Church
On Family.ChurchId = Church.ChurchId and Church.IsDeleted = 0
Left Outer Join FamilyPortrait
On Family.FamilyId = FamilyPortrait.FamilyId and FamilyPortrait.IsDeleted = 0
Left Outer JOIN MemberType
ON Member.MemberTypeId = MemberType.MemberTypeId And MemberType.IsDeleted = 0
Left Outer JOIN MemberStatus
ON Member.MemberStatusId = MemberStatus.MemberStatusId And MemberStatus.IsDeleted = 0

Where (Church.ChurchId = @ChurchId or @ChurchId is null )
And
(MemberType.MemberTypeId in (Select * from dbo.Split(@MemberTypeId,',')) or @MemberTypeId is null)
And
(MemberStatus.MemberStatusId in (Select * from dbo.Split(@MemberStatusId,',')) or @MemberStatusId is null )
And
Family.FamilyName between @StartFamilyName And @EndFamilyName
And
FamilyPortrait.IsCurrent = 1
And
FamilyPortrait.FamilyPortraitPath Is Not Null

End

Thanks in advance!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-30 : 12:26:03

You haven't said what sort of trouble you are having now. Please post any error messages or differences between expect results and results obtained

mbooher
Starting Member

3 Posts

Posted - 2014-12-30 : 12:51:17

Of course that would help. I'm sorry.

These are messages I receive when I try to create (execute) this stored procedure.

Msg 207, Level 16, State 1, Procedure rpmb_FamilyPortraitDirectoryP1Test, Line 44
Invalid column name 'MemberTypeId'.
Msg 207, Level 16, State 1, Procedure rpmb_FamilyPortraitDirectoryP1Test, Line 46
Invalid column name 'MemberStatusId'

The column names are valid. I had trouble also joining the Church table to the Member table on ChurchId, which is also a valid column name. I joined that to family instead.

I have used these table joins as is on many queries, so I believe that this must have something to do with this area.

(
Select distinct ST2.FamilyId,
(
Select ST1.Preferred + ', ' AS [text()]
From Member ST1
Where ST1.FamilyId = ST2.FamilyId
ORDER BY ST1.FamilyId
For XML PATH ('')
) FMembers
From Member ST2
)
Member

Any help or shove in the right direction would be wonderful.

Michele

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-30 : 13:05:10

I think it is as you say. The subquery that you have aliased as Member does not have a MemberTypeId or MemberStatusId column

mbooher
Starting Member

3 Posts

Posted - 2014-12-30 : 15:21:38

Thank you. I really appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources