Merging two fields from different tables together??? 1

[dg3249]

I have an orders table and a orders_contractor table, the orders table has a start and finish date field and the orders_contractor table also has the same fields. It seems that if an order has contractors that are extended beyond the original finish date that is shown in the orders table, it will then populate the orders_contractor table.
Therefore the contractor in the orders table has one finish date and the orders_contractor table will have another.

I have tried to create a query that would merge these fields together but have been unsuccessful.

 

[skuhlman]

When you say "merge these dates together" I assume you're trying to get the latest of the two dates or the first of the two dates. Both can be done in a similar fashion. This example grabs the key field and both dates as well as the minimum of the two and the maximum of the two

Select A.keyfield,
A.datefield,
B.datefield,
(case when A.datefield>B.datefield then A.datefield else B.datefield end) as MaxDate,
(case when A.datefield<B.datefield then A.datefield else B.datefield end) as MinDate
from A join B on a.keyfield = b.keyfield

 

[dg3249]

Thank you for your help, this worked perfectly or :->