I created a view table in my database, something like this:

TABLE A :

TABLE B :

my view query :

    SELECT
    a.cproject,
    contract,
    plan,
    CAST(plan / contract* 100 AS DECIMAL (6,3)) AS plan_pr,
    actual,
    CAST(actual / contract* 100 AS DECIMAL (6,3)) AS actual_pr,
    period,
    a.date
    FROM a.cproject, 
    LEFT JOIN b ON a.cproject = b.cproject AND a.date = b.date 
    GROUP BY a.cproject, a.period, MONTH(a.date), YEAR(a.date)

From what I have done above I have a problem, where when only table B has data updates / additions while table A does not, the contract_value in table B displays a null value in the view :

because table A is updated every week while in table B the data is updated randomly (Uncertain), and with the calculation process from table A with Table B there, is it possible for me to create a query flow that if the data in table A is updated and table B is not, then I can use the most recent contract data based on the date from table B, or is there another way I can do for my case above.

I'm still not very good at writing queries in mysql, I hope someone is kind enough to help me. thanks.

CodePudding user response：

You can use an inner join instead of a left join to discard any records in table A that don’t match records in Table B.

There may also be a typo in your query FROM a.cproject, doesn’t make much sense. Together, those lines should be:

…
FROM a
INNER JOIN b ON a.cproject = b.cproject AND a.date = b.date 
…

CodePudding user response：

i suggest you to use this if you need all the history data to be seen

create table #A (
cproject varchar (10),
planid numeric (16,0),
actual numeric (16,0),
periodid varchar(2),
dateid date,
)

insert into #A values ('P001',  216858366634,   149092654604,   'W1',   '2022-04-07')
insert into #A values ('P001',  225529459769,   157092654604,   'W2',   '2022-04-14')
insert into #A values ('P001',  225529459769,   165087265700,   'W3',   '2022-04-21')

create table #B (
cproject varchar (10),
contractid numeric (16,0),
dateid date
)

insert into #B values ('P001',  405191268000,   '2022-04-07')
insert into #B values ('P001',  407156268000,   '2022-04-14')

SELECT
    a.cproject,
    isnull(b.contractid,c.contractid) as contractid,
    a.planid,
    CAST(planid / isnull(b.contractid,c.contractid)* 100 AS DECIMAL (6,3)) AS plan_pr,
    a.actual,
    CAST(actual / isnull(b.contractid,c.contractid)* 100 AS DECIMAL (6,3)) AS actual_pr,
    periodid,
    a.dateid
    FROM #A a
    LEFT JOIN #B b ON a.cproject = b.cproject AND a.dateid = b.dateid
    left join (select *,row_number() over(partition by cproject order by dateid desc) as rownum from #B) c on a.cproject = c.cproject and c.rownum = 1 -- get the latest date from Table B
    GROUP BY 
    a.cproject,
    isnull(b.contractid,c.contractid),
    a.planid,
    a.actual,
    periodid,
    a.dateid