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

Current and History transactions

I have three tables: Item (A), Item Transaction Current (B), and Item Transaction History (C). I need to pull information...

from both tables B and C for a specific item number. Table A is used to get the description of the item. What would the SQL look like to achieve this result?

A join between tables B and C would be wrong. Imagine several transactions in the Current table B involving store numbers, customer numbers, dates, etc. Then imagine additional transactions in the History table C with different store numbers, customer numbers, dates, etc. The only thing these sets of transactions would have in common is the item number, and if you attempted to do a join, you'd end up with a cross join effect. This would not necessarily be wrong, but certainly inefficient, and decidedly clumsy if you wanted information such as number of sales per month, since you'd have repeated information from many dates joined with other dates on the same rows. If you know what I mean.

The solution you're looking for is a UNION:

select B.itemno
     , A.itemdescr
     , B.custno
     , B.storeno
     , B.transdate
  from TransCurrent B
inner
  join Item A
    on B.itemno = A.itemno
 where B.itemno = number    
union all      
select C.itemno
     , A.itemdescr
     , C.custno
     , C.storeno
     , C.transdate
  from TransHistory C
inner
  join Item A
    on C.itemno = A.itemno
 where C.itemno = number  

Use UNION ALL instead of UNION, since there will never be duplication between transactions in Current and History tables (UNION does a sort on the entire result set from both queries, looking for duplicates).


This was last published in December 2003

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

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close