Solved

LEFT JOIN with Multiple lookups to same lookup table

Posted on 2011-09-30
4
263 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
ID: 36893702
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
ID: 36893837
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
ID: 36893884
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
ID: 36893995
Nice!  Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now