Query problems in mysql

Asked

Viewed 29 times

0

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

my query is this.

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 function dias($dia,'mes-anotoanomes') will convert the date of the variable coming day so 01-2018 to 201801.

It turns out that when there is no information in the commission table that query works. only that when recording month 1 data for ex when I will select another month for ex month 02-2018 it returns nothing

follows ss from my tables

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

could anyone assist me with this query ? Thank you .

1 answer

0

solved transforming this query into 4 querys 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
  • If you use 3 subselects in the same table with the same condition, why not use Join? which would look something like this: select 
 u.nome, 
 u.id as id_user,
 c.comissao,
 c.premio,
 c.meta
from usuarios u 
LEFT JOIN comisscao c
ON c.id_usuario = u.id
AND c.mes = '".dias($dia,'u.mes-anotoanomes')."'
where u.id_grupo='$resultg[id]' and u.acesso='1'
order by u.nome asc

  • I was using Join look there and was not returning what I wanted :(

Browser other questions tagged

You are not signed in. Login or sign up in order to post.