Hi,
I have three tables as below.
tbl1
eid qnt pb
12 3 1
13 1 1
23 3 1
tbl2
tid eid fx so
1 12 0 1
2 13 0 1
3 23 1 1
4 23 0 1
tbl3
tlid tid eid fx
1 1 12 0
2 1 12 0
3 2 13 0
4 3 23 1
5 4 23 0
Need an update query that counts the number of 'tlid' in 'tble3'
who's 'pb' in 'tb1' is '1'
who's 'eid' in 'tbl2' does not have '1' as one if it's 'fx'
and update 'so' in 'tbl2' to '0' if the total counted 'tlid' in 'tble3' is 'LESS' than the 'qnt' in 'tbl1'
The final result will be that 'so' for 'tid 1' in 'tbl2' will be '0'
The others will not get updated because:
The sum of 'tlid' in 'tbl3' for 'tid 2' or 'eid 13' in 'tble3' = 'qnt' in 'tbl1'
'tlid' for 'eid 23' in 'tbl3' will not be counted becuse 'eid 23' has '1' in one of the 'fx' in 'tbl2'
Appreciate you help
------------------------------------------------------------------
drop table if exists tbl1;
create table tbl1 (eid integer, qnt integer, pb integer);
insert into tbl1 values (2,3,1),(3,1,1),(23,3,1);
drop table if exists tbl2;
create table tbl2 (tid integer, eid integer, fx integer, so integer);
insert into tbl2 values (1,12,0,1),(2,13,0,1),(3,23,1,1),(4,23,0,1);
drop table if exists tbl3;
create table tbl3 (tlid integer, tid integer, eid integer, fx integer);
insert into tbl3 values (1,1,12,0),(2,1,12,0),(3,2,13,0),(4,3,23,1),(5,4,23,0) ;