Vahid Alvandi Vahid Alvandi - 5 months ago 8
SQL Question

compare result with other table mysql

I have 2 table I select and count compare item form 2 tables, also after compare I need to compute how many item contain in other table.

select
results.userid,
results.amount,
results.type,
results.counting
from
(SELECT
userid, amount, code, count(*) as counting
FROM
user_buys
join star ON (amount >= min_amount)
group by type
HAVING amount >= 1000) as results


with table below

userid |amount
----------------------
1 | 1000
2 | 2000
3 | 5500
4 | 8200
5 | 200
6 | 1500
7 | 800


I need to sync with other table

min_compare| min_amount | type
-----------------------------------
2 | 1000 | 1star
2 | 2000 | 2star
3 | 5000 | 3star
4 | 8000 | 4star
5 | 9000 | 5star
6 | 10000 | 6star
7 | 11000 | 7star


because we have

5 item larger 1000 it contain => 1star
3 item larger 2000 it contain => 2star
2 item larger 5000 it contain => 3star
1 item larger 8000 it contain => 4star


my expected result

rankin
--------
1star
2star
3star
4star

Answer

Try this;)

select
    star.type, star.min_amount, t.cnt, t.userids
from star
inner join (
    select  t1.type, count(t2.userid) as cnt, group_concat(t2.userid order by t2.userid) as userids
    from star t1
    inner join user_buys t2 on t1.min_amount <= t2.amount
    group by t1.type
) t on t.type = star.type
order by star.type

SQLFiddle DEMO HERE