combining the date from two tables?

aej1973
aej1973 used Ask the Experts™
on
Hi, I have three tables as shown below:

type table

order_id     order_type    
001               Order_A
002               Order_B

Order_A table

Order_id    order_date       name       comments
001             12/04/04         Jim          Comment_01
001             12/04/04         Jon          Comment_02
001             12/04/04         Bob          Comment_03
001             12/04/05         Bob          Comment_04

Order_B table

Order_id    order_date       name       comments
002             12/04/04         Jim          Comment_05
002             12/04/04         Jon          Comment_06
002             12/04/05        Bob          Comment_07
002             12/04/05         Bob          Comment_08

How can I write a query that will combine the output of both tables, for eg; I want to see all of Bob's orders on 12/04/05  and I need the output as follows:


Order                 order_date        name       comments
order_A             12/04/05           Bob          Comment_04
order_B             12/04/05           Bob          Comment_07
order_B             12/04/05           Bob          Comment_08

Thanks for the help.

A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try

select b.order_type, a.order_date, a.name, a.coment
from (
      select * from Order_A
      union all
      select * from Order_B
) a
inner join type b on a.order_id = b.order_id
where a.name = 'Bob' and a.order_date = '12/04/05'

Author

Commented:
Thank you. Works great.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial