Problem with query Join with 5 tables

Asked

Viewed 58 times

0

Good night,

I am trying to make a Join to 5 tables to fetch me the information so that it is not returning me some data.

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

  • What is the structure of your tables? Have you considered setting an example on Sqlfiddle? Anyway, I believe that [one of] your problem is that the Subqueries used are not earning a nickname so that they can be referenced in the ON (more details that question in Soen).

1 answer

3


When you make a subquery within a type junction:

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

There are two problems with her:

  1. To subquery will tell all the table rows, not only those where avaliacoes.estabelecimento_id = estabelecimentos.id;

  2. The junction will not be made, because in the clause ON there is no reference to the result of subquery.

From what I can assume of your intent by observing the code, probably what you’re 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 joint.

Browser other questions tagged php mysql join

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