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
  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
  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).

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.