Joining Tables to get Data... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Joining Tables to get Data...

Author

Topic

ill05nino
Starting Member

11 Posts

Posted - 2012-07-19 : 10:31:25

Here is my issue:

Im trying to run a report that in the end ties 2 specific pieces of data together. A persons name and their current status code based on a scheduled activity.

The problem that I am running in to is that the two pieces of data are tied together through a string of about 5 tables.

When trying to do my JOIN statements, I cannot for the life of me get the status code to show up.

Here is my structure:

Activity Table --
ActivityID and PeopleID

People Table --
People ID and NAME

Prospect Table --
PeopleID and ProspectID

Prospect Status --
ProspectID and StatusID

Status Table --
StatusID and StatusName

This is how I currently have the join section setup:

FROM Activity

LEFT OUTER JOIN People P1 ON Activity.PeopleIDY = P1.PeopleIDY
LEFT OUTER JOIN ProspectMain PM1 ON P1.PeopleIDY = PM1.MainContactPeopleIDY
LEFT OUTER JOIN ProspectStatus PS1 ON PM1.ProspectIDY = PS1.ProspectIDY
LEFT OUTER JOIN PickListValues PLV3 ON PS1.StatusPL = PLV3.PickListValueIDY

Am I missing something here? Thanks for any and all help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-19 : 11:12:38

>>Am I missing something here?
Just a SELECT clause and an explanation as to what the problem is

. I assume you are including PLV3.statusCode in your SELECT clause, right?
(specific error, no rows at all, rows but with nulls for status code, ?)

Is it possible that not all Activity rows have PeopleIDY, and/or not all People rows have MaincontactPeopleIDY, etc...
by the time you work through the chain all your outer joined status values have dropped off

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 11:16:02

you table structure and query doesnt match. for example you've different table names specified in query. give us correct table names with structure and sample data and then explain us what you want as output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ill05nino
Starting Member

11 Posts

Posted - 2012-07-19 : 12:06:35

IF @CommunityIDY IS NOT NULL
INSERT INTO #Activity
SELECT TOP 500
Activity.ActivityIDY,
U1.FullName UserName,
PLV1.ListCode PriorityCode,
PLV1.ListDescription PriorityDesc,
Convert(varchar,Activity.DateScheduled,102)+' '+Convert(varchar,Activity.DateScheduled,108) DateScheduled,
null,
ISNULL(P1.FirstName,'') + CASE WHEN P1.FirstName IS NULL OR P1.FirstName = '' OR P1.LastName IS NULL OR P1.LastName = '' THEN '' ELSE ' ' END + ISNULL(P1.LastName, '') AS ContactName,
ph1.PhoneNumber as ContactPhone,
ph1.CompanyDNCDate as ContactDNC,
PLV2.ListCOde ActivityCode,
PLV2.ListDescription ActivityCodeDesc,
PLV3.ListCode StatusCode,
null,
null,
Activity.ProspectIDY,
Activity.ReferralIDY,
0,
Activity.CommunityIDY
FROM Activity WITH (NOLOCK, INDEX(IX_Activity_ProspectIDY_DateCompleted))
LEFT OUTER JOIN [User] U1 ON Activity.ScheduledForUserIDY = U1.UserIDY
LEFT OUTER JOIN PickListValues PLV1 ON Activity.PriorityPL = PLV1.PickListValueIDY
LEFT OUTER JOIN People P1 ON Activity.PeopleIDY = P1.PeopleIDY
LEFT OUTER JOIN ProspectMain PM1 ON P1.PeopleIDY = PM1.MainContactPeopleIDY
LEFT OUTER JOIN ProspectStatus PS1 ON PM1.ProspectIDY = PS1.ProspectIDY
LEFT OUTER JOIN PickListValues PLV3 ON PS1.StatusPL = PLV3.PickListValueIDY (This is the line that should get me the Status Code from the PickListValues table)
LEFT OUTER JOIN PeopleAddress pa ON p1.PeopleIDY = pa.PeopleIDY
LEFT OUTER JOIN Phone ph1 ON pa.AddressIDY = ph1.AddressIDY AND ph1.DisplayOrder = 1
LEFT OUTER JOIN PickListValues PLV2 ON Activity.ActivityCodePL = PLV2.PickListValueIDY
LEFT OUTER JOIN Prospect ON Activity.ProspectIDY = Prospect.ProspectIDY
LEFT OUTER JOIN Referral ON Activity.ReferralIDY = Referral.ReferralIDY
INNER JOIN Address ON PA.AddressIDY = Address.AddressIDY AND (Address.IsSeasonal = 0 OR Address.IsSeasonal IS NULL)
WHERE
(((@ReturnProspects = 1) AND (Activity.ProspectIDY IS NOT NULL) AND (Prospect.DateExpired IS NULL) AND (PS1.DateExpired IS NULL))
OR ((@ReturnReferrals = 1) AND (Activity.ReferralIDY IS NOT NULL) AND (Referral.DateDeleted IS NULL)))
AND (((@UserIDY IS NOT NULL) AND (Activity.ScheduledForUserIDY = @UserIDY)) OR (@UserIDY IS NULL))
AND (((@ActivityTypePL IS NOT NULL) AND (Activity.ActivityTypePL = @ActivityTypePL)) OR (@ActivityTypePL IS NULL))
AND (((@DateFrom IS NOT NULL) AND (CONVERT(varchar, Activity.DateScheduled, 112) >= CONVERT(varchar, @DateFrom, 112))) OR (@DateFrom IS NULL))
AND (((@DateTo IS NOT NULL) AND (CONVERT(varchar, Activity.DateScheduled, 112) <= CONVERT(varchar, @DateTo, 112))) OR (@DateTo IS NULL))
AND (Activity.DateCompleted IS NULL)
AND (Activity.CommunityIDY = @CommunityIDY)
AND (Address.IsSeasonal = 0 OR Address.IsSeasonal is null)

This is what I have, I excluded my ELSE statement, but you get the main idea. It is pulling back ALL the information I need, except the Status Code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 13:10:25

first check do you've records existing in PLV3 satisfying the condition PS1.StatusPL = PLV3.PickListValueIDY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ill05nino
Starting Member

11 Posts

Posted - 2012-07-19 : 14:31:12

Hey visakh, yes those records do exist.

ill05nino
Starting Member

11 Posts

Posted - 2012-07-19 : 14:34:46

Just to show my expected output this is what I have...

Activity Table --
ActivityID = '1' and PeopleID = 'ABC'

People Table --
People ID = 'ABC' and NAME = 'John Doe'

Prospect Table --
PeopleID = 'ABC' and ProspectID = '100'

Prospect Status --
ProspectID = '100' and PickListValueID = '123'

Status Table --
PickListValueID= '123' and ListCode = 'A - Hot Lead'

Report should show:
Contact Name Status
John Doe A - Hot Lead

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 14:56:45

then it should be like


SELECT p.Name AS ContactName,s.ListCode AS Status
FROM People p
INNER JOIN Prospect pr
ON pr.PeopleID = p.PeopleID
INNER JOIN ProspectStatus ps
ON ps.ProspectID = pr.ProspectID
INNER JOIN Status s
ON s.PickListValueID = ps.PickListValueID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources