Sql query full join

Question

I have 2 tables in mariadb which are treatments and payments.

Treatments: id(int(11)), treatment_name(varchar(255)), treatment_date(date)

Payments: id(int(11)), treatment_id(int(11)), payment_amount(decimal(10,2)), payment_date(date)

I want to list records from both table for a given date range, for example from 2016-01-01 to 2016-06-06

1-If the treatment is in between the given date range and has payment OR 2-if the treatment isn't in between the date range BUT has payment with the payment date is in between the given date range.

Anyone can help me for setting the query?

Answer 1

If I understand correctly, you don't need a full outer join. You simply need an inner join along with the correct where clause:

select t.*, p.*
from treatments t inner join
     payments p
     on t.id = p.treatment_id
where t.treatment_date between '2016-01-01' and '2016-06-06' or
      p.payment_date between '2016-01-01' and '2016-06-06';

Answer 2

This can be done using UNION as follows,

SELECT * FROM Treatments WHERE treatment_date BETWEEN #01/01/2016# AND #06/06/2016#
UNION
SELECT * FROM  Payments WHERE treatment_date BETWEEN #01/01/2016# AND #06/06/2016#

use this as single Query.

Answer 3

To give you a start, the following will join both tables, you will need to add your filtering based on the date:

SELECT *
FROM Treatments as t1, Payments as t2
WHERE t1.id = t2.treatment_id;

I believe you can do the rest on your own.