Solved

LEFT JOIN with Multiple lookups to same lookup table

Posted on 2011-09-30
4
260 Views
Last Modified: 2012-05-12
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
Comment
Question by:ttist25
  • 2
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 1

Author Closing Comment

by:ttist25
Comment Utility
Nice!  Thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now