CODEWITHSUNDEEP
phpmysqlrow
Kelsey
Kelsey

Reputation: 921

Pulling Multiple Rows from Multiple Tables and Ordering Them Using One MySQL Query

I have two tables, purchased_event_tickets and membership_purchases, both are tracking payments made by customers for either event ticket purchases or membership renewal payments. Here is how the tables look, in theory:

Membership_purchases table:

order_id | total_amount | date_of_purchase   | memberid |
2        | 392.42       | 2015-09-23 9:30:23 | 23       |
3        | 423.23       | 2016-09-12 6:23:12 | 49       |

Purchased_event_tickets table:

order_id | total_amount | purchase_date      | memberid |
7        | 392.42       | 2014-09-23 9:10:23 | 23       |
1        | 423.23       | 2013-09-12 7:23:12 | 49       |

I think I can use the following query to pull all 4 rows from both tables using the following code:

SELECT *
FROM purchased_event_tickets, membership_purchases

However, I would like the four rows to be ordered by the date of purchase, and I am unsure how to do this when pulling from two tables.

I suppose I would be looking for something along the lines of:

SELECT * FROM purchased_event_tickets, membership_purchases ORDER BY purchase_date AND date_of_purchase DESC

I am not sure if that query would work, though, but if it did, it would return the rows in the following order:

order_id | total_amount | date               | memberid |
3        | 423.23       | 2016-09-12 6:23:12 | 49       |
2        | 392.42       | 2015-09-23 9:30:23 | 23       |
7        | 392.42       | 2014-09-23 9:10:23 | 23       |
1        | 423.23       | 2013-09-12 7:23:12 | 49       |

Does anyone know how I would obtain this type of ordering?

Upvotes: 0

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think you want union all, not a join:

SELECT order_id, total_amount, date_of_purchase as date, memberid
FROM purchased_event_tickets
UNION ALL
SELECT order_id, total_amount, purchase_date as date, memberid
FROM membership_purchases mp
ORDER BY date DESC;

Upvotes: 1

Related Questions