You could construct your query this way to retrieve orders placed yesterday:
select
ordhdr.customerID
, ordhdr.orderID
, ordhdr.orderDate
, orddtl.sku
, products.productName
from Table4 ordhdr
inner join Table5 orddtl on orddtl.customerid = ordhdr.customerid
inner join Table2 products on products.sku = orddtl.sku
where ordhdr.orderDate >= convert(date, getDate()-1)
and ordhdr.orderDate < convert(date, getDate())
/* target: OrdersPlacedYesterday */
Then if you took the results of this and referenced a Master data extension you could get a distinct list of subscribers for your sending audience:
select
m.emailaddress
, m.customerid
from MasterDataExtension m
where exists (
select top 1.*
from OrdersPlacedYesterday orders
where orders.customerid = m.customerid
)
/* target: OrdersPlacesYesterday_Send */