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