CODEWITHSUNDEEP
mysqljoin
Rawr
Rawr

Reputation: 2224

Join two tables and order by single column

Lets say I have two tables, one of customers and one of orders. The goal is to show a timeline of when customers joined along when orders where created.

Customer

id name created
1  John 1
2  Mary 3
3  Bob  5

Order

id   created
aaa  2
bbb  4
ccc  6

Combined

id  name  created
1   John  1
aaa       2
2   Mary  3
bbb       4
3   Bob   5
ccc       6

Originally I started with a join table that held the ids of each table and the date they were created. I'm wondering if there's a more efficient way of joining these two tables and ordering by the date created.

Alternatively, I could look up the last X records and manually merging the results in the code. Looking for advice here.

Upvotes: 0

Views: 48

Answers (1)

Nick
Nick

Reputation: 147146

If I understand your question correctly, this query should give you the results you want. It produces a list of Customers and Orders, sorted by created. Rows are identified by a 'Customer' or 'Order' value:

SELECT *
FROM (SELECT 'Customer' AS type, id, name, created FROM Customer
      UNION ALL
      SELECT 'Order', id, '', created FROM Orders) u
ORDER BY created, type

Output:

type        id      name    created
Customer    1       John    111
Order       aaa             111
Customer    2       Mary    222
Order       bbb             222
Customer    3       Bob     333
Order       ccc             333

Upvotes: 1

Related Questions