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?
精彩评论