1

Topic: Choice from two tables

All greetings;
Such question, we simplify the task to two tables
PLAN
id INT;
date DATE;
SUMMA DECIMAL
FAKT
id INT;
date DATE;
SUMMA DECIMAL
Not all dates are in tables, can be either the plan or the fact or both or there is nothing.
It is necessary to make the request which result will be type

-----------+--------+--------
date! plan! fakt
-----------+--------+--------
11/5/2017! 100.00! 150.00
-----------+--------+--------
11/6/2017!! 150.00
-----------+--------+--------
11/8/2017! 125.00!
-----------+--------+--------
11/9/2017!! 250.00
-----------+--------+--------
11/13/2017! 100.00! 150.00

Thanks

2

Re: Choice from two tables

Alex-777 wrote:

All greetings;
Such question, we simplify the task to two tables
PLAN
id INT;
date DATE;
SUMMA DECIMAL
FAKT
id INT;
date DATE;
SUMMA DECIMAL
Not all dates are in tables, can be either the plan or the fact or both or there is nothing.
It is necessary to make the request which result will be type

-----------+--------+--------
date! plan! fakt
-----------+--------+--------
11/5/2017! 100.00! 150.00
-----------+--------+--------
11/6/2017!! 150.00
-----------+--------+--------
11/8/2017! 125.00!
-----------+--------+--------
11/9/2017!! 250.00
-----------+--------+--------
11/13/2017! 100.00! 150.00

Thanks

select
z.date
,p.summa
,f.summa
from
(
select date
from PLAN
union
select date
from FACT
) z
left join PLAN p on p.date = z.date
left join FAKT f on f.date = z.date
... that to DATE - a unique field in both tables;
If is not present - it is necessary to finish conditions....

3

Re: Choice from two tables

javajdbc wrote:

it is passed...
select
z.date
,p.summa
,f.summa
from
(
select date
from PLAN
union
select date
from FACT
) z
left join PLAN p on p.date = z.date
left join FAKT f on f.date = z.date
... that to DATE - a unique field in both tables;
If is not present - it is necessary to finish conditions....

Date field not unique, but the combination date, id_client, time is unique. Date and time separately as in the schedule is not present date, and there is a day of week and time. Well and in the fact day of week is changed for specific date and time.

4

Re: Choice from two tables

Alex-777 wrote:

it is passed...
Date field not unique, but the combination date, id_client, time is unique. Date and time separately as in the schedule is not present date, and there is a day of week and time. Well and in the fact day of week is changed for specific date and time.

...What question such and the answer...
...Put the problem more precisely, lead specific structures;
The specific relation of structures, rule business
(Whether the client in one day two times can have the fact?
Whether it is possible to have the fact NOT in day of the schedule, and all such hogwash)
Example of the data and expected result...

5

Re: Choice from two tables

SELECT sq.date, t1.plan, t2.fakt
FROM (SELECT date FROM plan
UNION
SELECT date FROM fakt
) AS sq
LEFT JOIN plan AS t1 ON sq.date = t1.date
LEFT JOIN fakt AS t2 ON sq.date = t2.date

6

Re: Choice from two tables

...

SELECT sq.date, SUM (t1.plan) AS plan, SUM (t2.fakt) AS fakt
FROM (SELECT date FROM plan
UNION
SELECT date FROM fakt
) AS sq
LEFT JOIN plan AS t1 ON sq.date = t1.date
LEFT JOIN fakt AS t2 ON sq.date = t2.date
GROUP BY sq.date

7

Re: Choice from two tables

Alex-777;
full outer join

8

Re: Choice from two tables

MasterZiv , FULL JOIN? In MySQL?

9

Re: Choice from two tables

Akina wrote:

MasterZiv , FULL JOIN? In MySQL?

If is not present, to imitate through UNION ALL...
p.s. I that  should know all by heart?