Problems with query in mysql

Navigation
0

Good afternoon. I have a query in my db that has to bring the user name and id data from the users table and the commission table brings the commission, award, month, and a month field consisting of year and month ex 201801.

My query is this one. 

select u.nome, u.id as id_user, c.*
from usuarios u 
left join comissao c on c.id_usuario=u.id
where (c.mes='".dias($dia,'mes-anotoanomes')."' or c.mes IS NULL) and id_grupo='$resultg[id]' and acesso='1'
order by u.nome asc;

where the dias($dia,'mes-anotoanomes') function will convert the date of the next day variable so 01-2018 to 201801.

It happens that when there is no information in the commission table this query works. but when recording the data of month 1 for ex when I go to select another month for ex month 02-2018 it does not return anything

follows ss from my tables

Can anyone help me with this query? Thank you.

    
asked by anonymous 22.01.2018 / 21:18

1 answer

0

I decided to transform this query into 4 queries, being 3 subs 

select 
                                    u.nome, u.id as id_user,
                                    (select comissao from comissao where id_usuario=u.id and mes='".dias($dia,'mes-anotoanomes')."') as comissao,
                                    (select premio from comissao where id_usuario=u.id and mes='".dias($dia,'mes-anotoanomes')."') as premio,
                                    (select meta from comissao where id_usuario=u.id and mes='".dias($dia,'mes-anotoanomes')."') as meta
                                    from usuarios u where u.id_grupo='$resultg[id]' and acesso='1'
                                        order by u.nome asc
    
22.01.2018 / 21:38
Separate email body from within php tag Look up numbers in the multidimensional array (multiple layers)