Bring Records That Do not Have - 3rd Table

Question

Bring Records That Do not Have - 3rd Table

Navigation

#1 by (1 votes)

0

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 PK ID_a .

The second table has PK ID_b and FK ID_A .

The third table has FK ID_B and ID_D .

The fourth table has PK ID_D .

And it has 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 the data in TABLE D that you do not have in TABLE C in the query.

For example, the ID 1 of TABLE A has a foreign record in TABLE B . And the ID of TABLE B has records in TABLE C . And in TABLE C has the ID of TABLE D .

In this example above, TABLE C is only populated with information with ID 1 from TABLE D .

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

Ihadtocreateafourthtablebecausetheresultsarecomingupwiththevalueoftherepeatedcolumn,obviously.

I'vecreatedatablenamedCATEGORY_SEGMENTSwithCategoryID.Asyoucanseeintheimagebelow,thepatterncontinues...therewillalwaysbe9lines.ButtheSegmentcolumnmustbethevalueyouhaveinthistable.InthetableDEALER_RATING_CATEGORIESIcreatedthecolumnID_CATEGORIA_SEGMENTOtobeabletoconnectwiththisnewtable.

ImadeaLEFTJOINinthequerythatyousetup,butitdidnotwork.

UPDATE

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

mysql

asked by anonymous 25.05.2016 / 21:01

1 answer

where affecting only one field in select Problem with mysql

score 1 · Accepted Answer

Now, 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 JOIN using the thread that is a varchar field.

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 did the UNION suggested, in practice we will have all the TABLE_D - categories records. So by "inverting" your SELECT, based on the main table being TABLE_D, we came up with a better solution. Following:

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 is useful.

PREVIOUS ANSWER

Using the EXISTS clause would not 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 );