Duplicate xReference Fields in 1 query ?

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.

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 :

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 ?

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, MemberIDSince 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 aliasesSELECT 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:)|||EXCELLENT !

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

THANK YOUsql