How to select latest records from multiple tables Mysql Php

I have three tables and i want to get records according to datetime for all tables,

Here is my table "users"

id      name 1       abc 2       xyz 

Here is my table "paymentHistory"

id      bookingId           userId          createdOn 1       101                 1               2020-07-10 12:11:14 2       102                 1               2020-07-11 10:31:19  3       105                 1               2020-07-11 12:31:19  4       109                 2               2020-07-10 11:45:32  

Here is my table "cancelPaymentHistory"

id      bookingId           userId          createdOn 1       103                 1               2020-07-07 11:31:28 2       100                 1               2020-07-11 11:31:28 3       109                 2               2020-07-08 19:28:41 

Here is my table "usr_booking"

id      bookingId           userId          status          created_on 1       104                 1               Inprocess       2020-07-07 10:31:28 

Now i want to get all record where userId=’1′ and according to createdOn (DESC) of three tables How can i do this ?

I tried with following code but showing so many records( showing wrong result)

SELECT u.id as userId,ph.bookingId as paymentHistoryBooking,cph.bookingId as CancelBookingId,ub.bookingId as usrBooking FROM users u JOIN paymentHistory ph ON u.id=ph.userId JOIN cancelPaymentHistory cph ON u.id=cph.id JOIN usr_booking ub ON u.id=ub.userId WHERE u.id='152' 
Add Comment
1 Answer(s)

Your query is not correct. First you miss the alias u of the users table so the second row must look like:

FROM users u

Second, in the the fourth row you accidently wrote u.id = cph.id, but you want to match with the userId, not the cancelPaymentHistory.id. Additionally you miss the alias for the cancelPaymentHistory table. So the correct fourth line must look like:

JOIN cancelPaymentHistory cph ON u.id = cph.userId

The whole query would look like:

SELECT u.id as userId,ph.bookingId as paymentHistoryBooking,cph.bookingId as CancelBookingId,ub.bookingId as usrBooking  FROM users u JOIN paymentHistory ph ON u.id=ph.userId JOIN cancelPaymentHistory cph ON u.id=cph.userId JOIN usr_booking ub ON u.id=ub.userId WHERE u.id='152'; 

To order your result by the latest createdOn of all tables, you must first identify the latest createdOn of all tables:

SELECT userId, max(createdOn) as latestActivity (SELECT userId, max(createdOn) as createdOn FROM cancelPaymentHistory UNION SELECT userId max(createdOn) as createdOn FROM usr_booking UNION  SELECT userId max(createdOn) as createdOn FROM paymentHistory) 

This result must be joined with the prior query:

SELECT u.id as userId,ph.bookingId as paymentHistoryBooking,cph.bookingId as CancelBookingId,ub.bookingId as usrBooking  , max(latestActivity) FROM users u JOIN paymentHistory ph ON u.id=ph.userId JOIN cancelPaymentHistory cph ON u.id=cph.userId JOIN usr_booking ub ON u.id=ub.userId JOIN (SELECT userId, max(createdOn) as latestActivity      FROM (SELECT userId, max(createdOn) as createdOn      FROM cancelPaymentHistory      UNION      SELECT userId, max(created_on) as created_on      FROM usr_booking      UNION       SELECT userId, max(createdOn) as createdOn      FROM paymentHistory) a) activity ON activity.userId = u.id WHERE u.id='1' ORDER BY activity.latestActivity DESC; 

Note, that I still got the ORDER BY in the query. I am assuming you want to run this statement for multiple users, so you can modify the WHERE-Statement like WHERE u.id IN ('1','2','3') and still have a sorted list.

I have set up a fiddle for you http://sqlfiddle.com/#!9/ea835a/28/0

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.