Reputation: 3233
I have 2 tables in MYSQL that will be joined by a foreign key. Each table has its own date field. What I would like to do is combine these two date fields together and then order by the combined information. I understand that you can order by multiple fields but I don't think that combines them.
Example
Join by tickets.ticket_id = ticket_history.ticket_id
Table 1
ticket_date
Table 2
ticket_history_date
Goal combine the two date fields and then order the rows by the combined dates
Upvotes: 1
Views: 3961
Reputation: 1202
The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.
Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION query is:
select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables;
Upvotes: 1
Reputation: 6126
Let's assume that you want the latest of the two dates, you can do something like this
SELECT
[some info],
GREATEST( ticket_date, ticket_history_date ) as latest_date
FROM
[tables and join]
ORDER BY
latest_date
If you wanted the oldest date you could use LEAST rather than GREATEST. If one of the dates might be NULL then you might want to look into COALESCE
Upvotes: 6