Q
Problem solve Get help with specific problems with your technologies, process and projects.

Two "copies" of a lookup table in the same query

I have tables with the following structure:

Model
 model_seq_num
 boxlable_model_desc
 partnumber
 agencypower_id1
 agencypower_id2

  
Agencypower
 Agencypower_id
 Agencypower_desc

The query I wrote:

select m.model_seq_number,
m.boxlabel_model_desc, 
m.partnumber, 
ap.agencypower_desc 
from model m, 
agencypower ap 
where m.agencypower_id1 = 
ap.agencypower_id

This query would give me all I want, except for the agencypower_desc corresponding to agencypower_id2. How do I modify this query to get that result as well? Any help is highly appreciated.


You just need to have two "copies" of the lookup table in the query. Use table alias names to distinguish them, and column alias names to distinguish the columns in the result set.

select m.model_seq_number
     , m.boxlabel_model_desc
     , m.partnumber
     , ap1.agencypower_desc  as agencypower1
     , ap2.agencypower_desc  as agencypower2
  from model m
inner
  join agencypower ap1
    on m.agencypower_id1
     = ap1.agencypower_id
inner
  join agencypower ap2

    on m.agencypower_id2
     = ap2.agencypower_id

For More Information


This was last published in May 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close