Duplicate xReference Fields in 1 query ?

kropes2001 · #1 (**permalink**) 04-10-07, 02:42

HI

Its been a while since i have posted here, but this one has stumped me. maybe i am just super hungry right now and missing something obvious... but i can get this one.

i would search the forums for the answer, but not even sure what to search for.... not even sure if this (left joins) is the way to accomplish what i want.

Using MS-SQL database as the back end.

I have 3 data tables.
Table 1 is a cross reference table.
Table 2 is the primary data (group data)
Table 3 is the secondary data (users data) (multiple users in a group)

table 1 provides the text that matches the respective status from both the 2nd and 3rd tables.

Code:

xRefTable 
	MemberStatus	MemberStatusText
	------------	----------------
	0 		Non Member
	1 		Referral
	2 		Resident
	3		Non Resident

PrimaryUserTable
	GroupID		GroupStatus
	------		------------
	1 		0
	2 		3
	3 		1
	4 		2

SecondaryDataTable
	MemberID	GroupID		MemberStatus
	--------	-------		------------
	1		1		0
	2		1		0
	3		1		0
	4		1		0
	5		2		3
	6		2		3
	7		3		1
	8		3		1
	9		4		2
	10		4		1
	11		4		1
	12		4		3

The ultimate results i am trying to get is something like :

Code:

The Results should be able to yield

	GroupID	GroupStatus	GroupStatusText		MemberID	MemberStatus	MemberStatusText
	-------	-----------	---------------		--------	------------	----------------
	4	2		Resident		9		2		Resident	
	4	2		Resident		10		1		Referral	
	4	2		Resident		11		1		Referral	
	4	2		Resident		12		3		Non Resident

i am trying to do this with a single SQL query, i am sure it is possible, but cant finger out how to structure it. this is what i was thinking, but i am sure you can see its not going to work.

Lines 4 and 7 are the hangup i believe.
Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?

Code:

1	SELECT 	
2	PrimaryUserTable.GroupID, 
3	PrimaryUserTable.GroupStatus, 
4	xRefTable.MemberStatusText as GroupStatusText

5	SecondaryDataTable.MemberID,
6	SecondaryDataTable.MemberStatus,
7	xRefTable.MemberStatusText as MemberStatusText

8	LEFT JOIN SecondaryDataTable on (PrimaryUserTable.GroupID = SecondaryDataTable.GroupID)
9	LEFT JOIN xRefTable on (PrimaryUserTable.GroupStatus = xRefTable.MemberStatus)
10	LEFT JOIN xRefTable on (SecondaryDataTable.MemberStatus = xRefTable.MemberStatus)

11	WHERE GroupID = 4
12	ORDER BY GroupID, MemberID

r937 · #2 (**permalink**) 04-10-07, 05:57

Quote:

Originally Posted by kropes2001

Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?

using table aliases

Code:

SELECT PrimaryUserTable.GroupID
     , PrimaryUserTable.GroupStatus
     , GroupXref.MemberStatusText as GroupStatusText
     , SecondaryDataTable.MemberID
     , SecondaryDataTable.MemberStatus
     , MemberXref.MemberStatusText as MemberStatusText
  FROM PrimaryUserTable
INNER
  JOIN SecondaryDataTable 
    on SecondaryDataTable.GroupID = PrimaryUserTable.GroupID
INNER
  JOIN xRefTable as GroupXref
    on GroupXref.MemberStatus = PrimaryUserTable.GroupStatus 
INNER
  JOIN xRefTable as MemberXref
    on MemberXref.MemberStatus = SecondaryDataTable.MemberStatus 
 WHERE PrimaryUserTable.GroupID = 4
ORDER 
    BY PrimaryUserTable.GroupID
     , SecondaryDataTable.MemberID

kropes2001 · #3 (**permalink**) 04-12-07, 22:00

EXCELLENT !

exactly what i needed. the table alisases were just skipping from my mind that night.

THANK YOU

Internet Services

Web Development

Digital Marketing

Consumer Tech