Problem with join query with 5 tables

Question

Problem with join query with 5 tables

Navigation

#1 by (3 votes)

0

Good evening,

I'm trying to do a join to 5 tables to get me the information so that it is not returning any data to me.

Code

SELECT * FROM estabelecimentos 
INNER JOIN estabelecimentos_anexos
    ON estabelecimentos_anexos.id_mae = estabelecimentos.id
INNER JOIN (SELECT COUNT(*) AS id FROM avaliacoes)
    ON avaliacoes.estabelecimento_id = estabelecimentos.id
INNER JOIN (SELECT COUNT(*) AS id FROM estabelecimentos_anexos)
    ON estabelecimentos_anexos.id_mae = estabelecimentos.id
INNER JOIN (SELECT COUNT(*) AS id_post FROM posts)
    ON posts.estabelecimento_id = estabelecimentos.id
INNER JOIN (SELECT ROUND((SUM(pontos) / COUNT(*)), 1) FROM avaliacoes)
    ON avaliacoes.estabelecimento_id = estabelecimentos.id  
WHERE estabelecimentos.activo = 1

php mysql join

asked by anonymous 24.03.2015 / 23:57

1 answer

How to copy a datagrid to an arraylist How to transform data into array with JSON?

score 3 · Accepted Answer

When you do a subquery within a type join:

INNER JOIN (SELECT COUNT(*) AS id FROM avaliacoes)
    ON avaliacoes.estabelecimento_id = estabelecimentos.id

There are two issues with it:

The subquery will count all table rows, not only those where avaliacoes.estabelecimento_id = estabelecimentos.id ;

From what I could presume of your intent by looking at the code, what you are probably trying to do is something like:

INNER JOIN (    SELECT estabelecimento_id as id, COUNT(*) AS count
                FROM avaliacoes
                WHERE estabelecimento_id = estabelecimentos.id
                GROUP BY estabelecimento_id
           ) apelido1
    ON apelido1.id = estabelecimentos.id

This would need to be done for each subquery within a join.