Discussion Locked

SQL Query Question

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have two inventory transaction tables. One is a history table, and the other is a current table. What would the SQL look like so that I can get the results from both tables for a specific item number?

Thanks.

3 replies

by o2

Use 'UNION', like:
SELECT c1, c2,..., cN
FROM current_table
WHERE item_id = your_item_id
UNION
SELECT C1, C2,..., CN
FROM archive_table
WHERE item_id = your_item_id;

Good luck
Oleg

by bk3004

Clearly this will need a join on both tables. Something on the lines of following.

If its a one time query following is good instead of parameters since it will give a good query plan.

Also see if you have index on ITEM_ID else it might end up table scanning.

select *
from TRAN_HIST A, TRAN_CURRENT B
where A.ITEM_ID='ABCD'
and B.ITEM_ID='ABCD'

This discussion is closed to new replies.