CODEWITHSUNDEEP
phpmysql
Abhijit Borkakoty
Abhijit Borkakoty

Reputation: 37

get records from two different tables based on their respective datetime fields - mysql

I am trying to fetch data from two different tables orders and order_returns by comparing their respective datetime field.

Below is the schema

ORDERS table
    order_id | date_1 | amount
       1      10-10-18    50
       2      11-10-18    100
       3      13-10-18    200

Order returns table

order_return_id | order_id | date_2
     1              3       13-10-18

The output should be

order_id  | order_return_id  | amount | date_1  | date_2
  1               NULL           50      10-10-18   NULL
  2               NULL           100     11-10-18   NULL
  3               1              200     13-10-18   13-10-18

I know this could be achieved by doing a left join but I want to display the result in this way. Below is the link

the order_returns data should appear when the date_1 matches date_2

https://ibb.co/kfX18p

The red part should appear where the arrow points

Upvotes: 0

Views: 27

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Use left join

select order_id , order_return_id, amount,date_1 ,date_2
from ORDERS left join Orderreturns
on date_1=date_2

Upvotes: 1

Related Questions