开发者

there are INNER JOIN limits?

开发者 https://www.devze.com 2023-01-23 08:05 出处:网络
There are a limit to use INNER JOIN? Original Query (Work just fine) I开发者_JS百科 got 77 rows affected

There are a limit to use INNER JOIN?

Original Query (Work just fine) I开发者_JS百科 got 77 rows affected

    SELECT atendimento.id, atendimento.responsavel, atendimento.ocorrencia, 
           atendimento.idcontrato, cliente.nome as clinome, cliente.id as cliid, 
           atendimento.d_ini, usuario.apelido, tipos.descricao, tipos.id as tip 
      FROM atendimento 
INNER JOIN cliente ON atendimento.cliente=cliente.id 
INNER JOIN usuario ON atendimento.usuario=usuario.id 
INNER JOIN tipos ON atendimento.status=tipos.id 
     WHERE 1=1 AND (atendimento.status=10 OR atendimento.status=11) 
  ORDER BY tipos.id, atendimento.d_ini ASC

New Try: (Not working very well) it is limited to only 17 rows affected. The result here is the same except for the only 17 rows returned in my query.

    SELECT atendimento.id, atendimento.responsavel, atendimento.ocorrencia, 
           atendimento.idcontrato, atend_os.protocolo, atend_os.tecnico, 
           cliente.nome as clinome, cliente.id as cliid, atendimento.d_ini, 
           usuario.apelido, tipos.descricao, tipos.id as tip 
      FROM atendimento 
INNER JOIN atend_os ON atendimento.id=atend_os.protocolo 
INNER JOIN cliente ON atendimento.cliente=cliente.id 
INNER JOIN usuario ON atendimento.usuario=usuario.id 
INNER JOIN tipos ON atendimento.status=tipos.id 
     WHERE 1=1 AND (atendimento.status=10 OR atendimento.status=11) 
  ORDER BY tipos.id, atendimento.d_ini ASC

What is going wrong here? does someone know why the result change?

Thanks a lot for any info!


The difference between the two queries is that the new query is accessing the atend_os table as well. Thus, any values of atendimento.cliente that doesn't also exist as a cliente.id is being filtered out.

An INNER JOIN requires that the value exist in both tables or the row is discarded.


try

 SELECT atendimento.id, atendimento.responsavel, atendimento.ocorrencia, 
           atendimento.idcontrato, atend_os.protocolo, atend_os.tecnico, 
           cliente.nome as clinome, cliente.id as cliid, atendimento.d_ini, 
           usuario.apelido, tipos.descricao, tipos.id as tip 
      FROM atendimento 
INNER JOIN cliente ON atendimento.cliente=cliente.id 
INNER JOIN usuario ON atendimento.usuario=usuario.id 
INNER JOIN tipos ON atendimento.status=tipos.id 
LEFT JOIN atend_os ON atendimento.id=atend_os.protocolo 
     WHERE 1=1 AND (atendimento.status=10 OR atendimento.status=11) 
  ORDER BY tipos.id, atendimento.d_ini ASC

and see how many rows have atend_os.protocolo = NULL


Apparently this table atend_os does not contain a record for every atendimento. Perhaps you need a left join?

0

精彩评论

暂无评论...
验证码 换一张
取 消