locked
Need help on query from multiple tables using group by clause RRS feed

  • Question

  • User-1797368610 posted

    Hi,

    Trying to get the below result in query using group by from below 3 tables. if you notice there are two items in table 1. one item with color one another black. i thing we may use max(), min() function.

    Result:

    PROJECT_NUM INSPECT_DATE DEFI_CODE LIFE_SAFETY ON_PRIOR_REP REPAIR_TIME
    600002 6/8/2018 C * Y 10
    600002 6/8/2018 C * Y 8

    Table1:

    PROJECT_NUM BUILDING_ID INSPECT_DATE PHY_COND COND_CATEGORY DEFI_CODE LIFE_SAFETY ON_PRIOR_REP REPAIR_TIME
    600002 2559 6/8/2018 EXTERIOR Parapets C * N 10
    600002 2559 6/8/2018 EXTERIOR Foundation Walls M Y 8
    600002 2559 6/8/2018 EXTERIOR Facade M N 5
    600002 2559 6/8/2018 EXTERIOR Roofs M N 5
    600002 2559 6/8/2018 EXTERIOR Flashing C * N 8
    600002 2559 6/8/2018 EXTERIOR Drains M * Y 4
    600002 2559 6/8/2018 EXTERIOR Vents

    Table2:

    CATEGORY_ID COND_CATEGORY ITEM_INSPECTED CREATION_DATE LAST_UPDATE_DATE
    1 Parapets 1
    2 Facade 1
    3 Foundation walls 1
    4 Roofs 2
    5 Flashing 2
    6 Drains 2
    7 Vents 2

    Table3:

    ITEM_INSPECTED ITEM_DESCRIPTION ITEM_CATEGORY CREATION_DATE LAST_UPDATE_DATE
    1 Exterior Walls and foundation Exterior Items Inspected
    2 Roofs, flashing, vents Exterior Items Inspected
    3 Gutters, Downspouts, splash blocks Exterior Items Inspected
    4 Drives, parking lots, curbs Exterior Items Inspected
    5 Walks, steps, guardrails Exterior Items Inspected
    6 Fences, walls, gates Exterior Items Inspected
    Tuesday, May 15, 2018 3:13 PM

All replies

  • User269602965 posted

    Does Table 2 already include the values for ITEM_INSPECTED > 2??

    You have comma separated values in table 3 that you normalize into table 2, but then you create new values like parapet in table 2 and use in table 1.

    The design appears not relational database traditional design.

    Tuesday, May 15, 2018 4:32 PM