MySQL - Select specified record with some conditions multiple table

1716 views mysql
-4

I have a little trouble with a MySQL query, I have two tables tb_user and tb_trx and the last one is the result which I'm trying to do

What I want to do is to show id_user from tb_user which have level = 0 and if id_user in tb_trx have status = 0 more than equals 2, they will not appear in result.

answered question

Can you confirm what you want? Your example output table does not match your description.

2 Answers

11

Welcome to StackOverflow. We would normally expect you to show us what you have tried so far, as this is not a code writing service. But have a go with this (N.B. I haven't tested it):

select u.id_user from tb_user u
where u.level = 0
and (select count(*) from tb_trx t
     where t.id_user = u.id_user
     and t.status = 0) <= 2

Incidentally I don't think your data set would properly test the rule you have given (which I may have misunderstood, your statement of it is pretty unclear).

posted this
9

SELECT tb_user.id_user FROM tb_user 
LEFT JOIN tb_trx ON (tb_user.user_id = tb_trx.user_id AND tb_trx.status = 0) 
WHERE tb_user.level = 0 AND COUNT(tb_trx.id_trx) <= 2;

posted this

Have an answer?

JD

Please login first before posting an answer.