Need help on query from multiple tables using group by clause

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

Answer 1

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.