I have a problem when make a Sqlite query, to simply my situation: Let's say I have tables like this
Project Facility_category Facility_item Project_Facility_Relation
-------- -------- -------- ---------
id| name id | name id | name id | project_id | category_id | item_id
----------- --------------------- --------------------- ----------------
1 | Building A 1 | Relax Outdoor 1 | Square 1 | 1 | 1 | 1
2 | Apartment B 2 | School 2 | Kid Zone 2 | 1 | 1 | 2
3 | Swimming pool 3 | 1 | 1 | 3
4 | High School A 4 | 1 | 2 | 4
5 | University B 4 | 1 | 2 | 5
Now I made a query like this one:
SELECT pr.id, pr.name,
(
SELECT DISTINCT json_group_array(
json_object('id', qu.id,
'name', qu.name,
'item',
(
SELECT DISTINCT json_group_array(json_object('id', ifi.id, 'name', ifi.name))
FROM Project_Facility_Relation fa
INNER JOIN Facility_item ifi ON ifi.id = fa.facility_id
where fa.project_id = pr.id AND fa.qualily_id = qu.id
)
))
FROM Project_Facility_Relation fa
INNER JOIN Facility_category qu ON qu.id = fa.qualily_id
where fa.project_id = pr.id
) as Category
FROM Project pr
GROUP BY pr.id
The problem is in my result, Category column run multile time based on how many Facility_category exist in Project_Facility_Relation. In this case, "Relax Outdoor" appear 3 times in Facility_category, so in Category result, there's 3 elements duplicate. "School" 2 times.
Project
--------
id | name | Category
-----------
1 | Building A | [
{
"id": "1",
"name": "Relax Outdoor",
"item": [
{
"id": "1",
"name": "Square",
},
{
"id": "2",
"name": "Kid Zone",
},
{
"id": "3",
"name": "Swimming pool",
},
]
},
{
"id": "1",
"name": "Relax Outdoor",
"item": [
{
"id": "1",
"name": "Square",
},
{
"id": "2",
"name": "Kid Zone",
},
{
"id": "3",
"name": "Swimming pool",
},
]
},
{
"id": "1",
"name": "Relax Outdoor",
"item": [
{
"id": "1",
"name": "Square",
},
{
"id": "2",
"name": "Kid Zone",
},
{
"id": "3",
"name": "Swimming pool",
},
]
},
{
"id": "2",
"name": "School",
"item": [
{
"id": "4",
"name": "High School A",
},
{
"id": "5",
"name": "University B",
},
]
},
{
"id": "2",
"name": "School",
"item": [
{
"id": "4",
"name": "High School A",
},
{
"id": "5",
"name": "University B",
},
]
}
]
Pls help me, show me where I am wrong? Many thanks