Solved

LEFT JOIN with Multiple lookups to same lookup table

Posted on 2011-09-30
4
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

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 74

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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