Bring Records You Don’t Have - 3° Table

Question

Scenario, four tables.

TABELA A  |  TABELA B    |      TABELA C     |       TABELA_D

   ID_A     ID_B   ID_A        ID_B   ID_D           ID_D   DESC
    1        10      1          10      1             1     PEÇAS
                                10      1             2     SERVIÇOS
                                10      1             3     COMPRA

The first table has the PK Id_a.

The second table has the PK Id_b and FK ID_A.

The third table has the FK ID_B and ID_D.

The fourth table has the PK ID_D.

And there’s this SELECT:

SELECT
    *
FROM
    CATEGORIES
WHERE
    EXISTS (
        SELECT
            `segmento`,
            `categoria`,
            dealer_rating_categories.id_categoria,
            IFNULL(
                round(
                    avg(
                        dealer_rating_categories.rating
                    )
                ),
                "NULL"
            ) AS media
        FROM
            DEALER_RATING_CATEGORIES
        JOIN DEALER_RATINGS ON DEALER_RATINGS.ID = DEALER_RATING_CATEGORIES.ID_AVALIACAO
        JOIN DEALERS ON DEALERS.ID = DEALER_RATINGS.ID_CONCESSIONARIA
        WHERE
            DEALERS.ID = 17
        AND DEALER_RATING_CATEGORIES.ID_CATEGORIA = CATEGORIES.ID
        GROUP BY
            dealer_rating_categories.id_categoria,
            segmento
    )
UNION
    SELECT
        *
    FROM
        CATEGORIES
    WHERE
        NOT EXISTS (
            SELECT
                `segmento`,
                `categoria`,
                dealer_rating_categories.id_categoria,
                IFNULL(
                    round(
                        avg(
                            dealer_rating_categories.rating
                        )
                    ),
                    "NULL"
                ) AS media
            FROM
                DEALER_RATING_CATEGORIES
            JOIN DEALER_RATINGS ON DEALER_RATINGS.ID = DEALER_RATING_CATEGORIES.ID_AVALIACAO
            JOIN DEALERS ON DEALERS.ID = DEALER_RATINGS.ID_CONCESSIONARIA
            WHERE
                DEALERS.ID = 17
            AND DEALER_RATING_CATEGORIES.ID_CATEGORIA = CATEGORIES.ID
            GROUP BY
                dealer_rating_categories.id_categoria,
                segmento
        )

I need to bring in the query the data of TABLE D that has not in the TABLE C.

To illustrate: the ID 1 of TABLE A has a foreign registration on TABLE B. And the ID of TABLE B has records in TABLE C. And in the TABLE C has the ID of TABLE D.

In this example above, the TABLE C is only filled with information with the ID 1 of TABLE D.

But I want to bring in the records query that are not in TABLE C, that are part of the TABLE D.

I had to create a fourth table because the results are coming with the value of the repeated column, obviously.

I created a table called CATEGORY_SEGMENTS with the Category ID. As you can see in the image below, the default continues... will always be 9 lines. But the Segment column has to be the value you have in this table. In the table DEALER_RATING_CATEGORIES i created the column ID_CATEGORIA_SEGMENTO to be able to link with this new table.

I made a LEFT JOIN in the query you built, but it didn’t work.

UPDATING

SELECT DISTINCT
    cs.segmento,
    c.categoria,
    cs.id,
    IFNULL(m.media, 0) AS media
FROM
    categories c
INNER JOIN dealer_rating_categories drc ON 1 = 1
RIGHT OUTER JOIN category_segments cs ON cs.id = drc.id_categoria_segmento
LEFT JOIN (
    SELECT
        DEALER_RATING_CATEGORIES.id_categoria_segmento,
        DEALER_RATING_CATEGORIES.ID_CATEGORIA,
        IFNULL(
            ROUND(
                AVG(
                    DEALER_RATING_CATEGORIES.RATING
                )
            ),
            0
        ) AS MEDIA
    FROM
        DEALER_RATING_CATEGORIES
    INNER JOIN DEALER_RATINGS ON DEALER_RATINGS.ID = DEALER_RATING_CATEGORIES.ID_AVALIACAO
    INNER JOIN DEALERS ON DEALERS.ID = DEALER_RATINGS.ID_CONCESSIONARIA
    WHERE
        DEALER_RATINGS.ID_CONCESSIONARIA = 17
    GROUP BY
        id_categoria_segmento
) m ON m.id_categoria = c.id
AND m.id_categoria_segmento = drc.id_categoria_segmento
GROUP BY
    cs.id
ORDER BY
    c.id,
    drc.segmento

Answer 1

Now yes, it is returning according to what you want, but if you allow me a suggestion, for this specific query, we could have a table for segment, because as you can see in the solution, I had to do the JOIN using the field segment that is a varchar.

SELECT DISTINCT
       drc.segmento, 
       c.categoria, 
       c.id,
       IFNULL(m.media, 0) AS media
  FROM categories c
 INNER JOIN dealer_rating_categories drc ON 1 = 1 
  LEFT JOIN ( SELECT
                    SEGMENTO,
                    DEALER_RATING_CATEGORIES.ID_CATEGORIA,
                    IFNULL(
                        ROUND(
                            AVG(
                                DEALER_RATING_CATEGORIES.RATING
                            )
                        ),
                        0
                    ) AS MEDIA
                FROM DEALER_RATING_CATEGORIES
               INNER JOIN DEALER_RATINGS ON DEALER_RATINGS.ID = DEALER_RATING_CATEGORIES.ID_AVALIACAO
               INNER JOIN DEALERS ON DEALERS.ID = DEALER_RATINGS.ID_CONCESSIONARIA
                WHERE
                    DEALER_RATINGS.ID_CONCESSIONARIA = 17
                GROUP BY
                    DEALER_RATING_CATEGORIES.ID_CATEGORIA,
                    SEGMENTO ) m ON m.id_categoria = c.id 
                                AND m.segmento = drc.segmento
 ORDER BY c.id, drc.segmento 

PREVIOUS ANSWER

Friend, as I had already posted to you in the comments, if we do the UNION suggested, in practice we will have all the records of TABELA_D - category. Then by "reversing" your SELECT, based on the main table being TABELA_D, we come to a better solution. Next:

SELECT `segmento`,
       `categoria`,
        dealer_rating_categories.id_categoria,
        IFNULL(
            round(
                avg(
                    dealer_rating_categories.rating
                )
            ),
            "NULL"
        ) AS media
  FROM categories TD
  LEFT JOIN dealer_rating_categories TC ON TC.id_categoria = TD.id
  LEFT JOIN dealer_ratings TB ON TB.id = TC.id_avaliacao
  LEFT JOIN dealers TA ON TA.id = TB.id_concessionaria 
 WHERE TA.id = 17
 GROUP BY TC.id_categoria, segmento

I hope this solution will be useful.

PREVIOUS ANSWER

Wouldn’t using the EXISTS clause solve your problem? Something like:

SELECT *
  FROM tabela_d
 WHERE NOT EXISTS( SELECT * 
                     FROM tabela_c 
                     JOIN tabela_b ON tabela_b.id_b = tabela_c.id_b
                     JOIN tabela_a ON tabela_a.id_a = tabela_b.id_a 
                    WHERE tabela_a.id_a = X
                      AND tabela_c.id_d = tabela_d.id_d );