• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

LEFT JOIN with Multiple lookups to same lookup table

Hello,

I'm working on a query in Microsoft Access SQL view as follows:
 
SELECT 
People_t.dem_LastName AS [Last Name], 
People_t.dem_FirstName AS [First Name], 
sup_Sex_t.sup_Sex_Description AS [Sex],
sup_Race_t.sup_Race_Description AS [Race],
sup_Citizenship_t.sup_Citizenship_Description AS [Citizenship Status],
sup_WidgetCategory_t.sup_WidgetCategory_Description AS [Primary Widget Type],
sup_WidgetCategory_t.sup_WidgetCategory_Description AS [Secondary Widget Type],

FROM (((((People_t 
LEFT JOIN sup_Sex_t ON People_t.sup_Sex = sup_Sex_t.sup_Sex_ID)
LEFT JOIN sup_Race_t ON People_t.sup_Race = sup_Race_t.sup_Race_ID)
LEFT JOIN sup_Citizenship_t ON People_t.sup_Citizenship = sup_Citizenship_t.sup_Citizenship_ID)
LEFT JOIN sup_WidgetCategory_t ON People_t.ci_PrimaryWidget_ID = sup_WidgetCategory_t.sup_WidgetCategory_ID)
LEFT JOIN sup_WidgetCategory_t ON People_t.ci_SecondaryWidget_ID = sup_WidgetCategory_t.sup_WidgetCategory_ID)

Open in new window


The last LEFT JOIN is the one giving me problems.  I assume it's because I'm double-dipping into the widgets table for primary and secondary widget types.  

What is the best way to handle something like this?  I have a third widget type to add as well.  

Thanks in advance!
0
ttist25
Asked:
ttist25
  • 2
1 Solution
 
sdstuberCommented:
try aliasing each instance of the reused table


  FROM people_t
      LEFT JOIN sup_sex_t
          ON people_t.sup_sex = sup_sex_t.sup_sex_id
      LEFT JOIN sup_race_t
          ON people_t.sup_race = sup_race_t.sup_race_id
      LEFT JOIN sup_citizenship_t
          ON people_t.sup_citizenship = sup_citizenship_t.sup_citizenship_id
      LEFT JOIN sup_widgetcategory_t as widget1
          ON people_t.ci_primarywidget_id = widget1.sup_widgetcategory_id
      LEFT JOIN sup_widgetcategory_t as widget2
          ON people_t.ci_secondarywidget_id = widget2.sup_widgetcategory_id
0
 
SheilsCommented:
Try:

 
SELECT 
People_t.dem_LastName AS [Last Name], 
People_t.dem_FirstName AS [First Name], 
sup_Sex_t.sup_Sex_Description AS [Sex],
sup_Race_t.sup_Race_Description AS [Race],
sup_Citizenship_t.sup_Citizenship_Description AS [Citizenship Status],
sup_WidgetCategory_t.sup_WidgetCategory_Description AS [Primary Widget Type],
sup_WidgetCategory_t.sup_WidgetCategory_Description AS [Secondary Widget Type],

FROM ((((People_t 
LEFT JOIN sup_Sex_t ON People_t.sup_Sex = sup_Sex_t.sup_Sex_ID)
LEFT JOIN sup_Race_t ON People_t.sup_Race = sup_Race_t.sup_Race_ID)
LEFT JOIN sup_Citizenship_t ON People_t.sup_Citizenship = sup_Citizenship_t.sup_Citizenship_ID)
LEFT JOIN sup_WidgetCategory_t ON People_t.ci_PrimaryWidget_ID = sup_WidgetCategory_t.sup_WidgetCategory_ID)
LEFT JOIN sup_WidgetCategory_t ON People_t.ci_SecondaryWidget_ID = sup_WidgetCategory_t.sup_WidgetCategory_ID

Open in new window

0
 
sdstuberCommented:
ooops!  I forgot to include the aliases of the selected results



widget1.sup_WidgetCategory_Description AS [Primary Widget Type],
widget2.sup_WidgetCategory_Description AS [Secondary Widget Type]
0
 
ttist25Author Commented:
Nice!  Thanks!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now