I have two tables
table:transaction
id . user_id . amount . status
1 - - 100 -- --- 500 - ----- 1
2 - - 100 -- --- 100 - ----- 0
3 - - 110 -- --- 200 - ----- 1
table:campaign
id . user_id . bid . status . budget . expense . size
1 -- 100 ---- 80 --- 1 ------- 200 ------ 200 ---- 5 --
2 -- 109 ---- 75 --- 1 ------- 050 ------ 030 ---- 2 --
3 -- 100 ---- 65 --- 1 ------- 700 ------ 065 ---- 2 --
4 -- 107 ---- 77 --- 0 ------- 020 ------ 020 ---- 2 --
5 -- 90 ----- 87 --- 1 ------- 120 ------ 090 ---- 7 --
I need campaign.id after filters using php and mysql
condition: if total money/transaction received from a user is greater than total expenses by a user. Money is also approved money (1=approved, 0=pending). Campaign is on means 1, who placed maximum bid
(sum(transaction.amount) WHERE transaction.status = 1 of that any user) > sum(campaign.expense of that user)
bid = max(bid)
Camps status = 1
1 step solution
mysql_query("SELECT campaign.* FROM campaign c,transaction t
WHERE budget>expense AND status='1' AND size='2'
ON c.user_id=t.user_id
HAVING SUM (CASE WHEN transaction.status=1 THEN transaction.amount ELSE 0 END) > SUM(campaign.expense)"));
not working
Alternatively if any solution in 3 steps -
1st step
mysql_query("SELECT id,user_id FROM campaign
WHERE budget>expense AND status='1' AND size='2'")
2nd step
keep id,user_id of 1st step WHERE SUM(transaction.amount status=1)
> SUM(campaign.expense)
3rd step
mysql_query("SELECT id,user_id FROM campaign
WHERE step2 satisfied")
Please help, I will sleep after solving it