Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119

    Unanswered: 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.

    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
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    EXCELLENT !

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

    THANK YOU
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •