【Question】

Is there a way to get one count of related items to the parent level and one count for the child level without breaking this into two queries?

I have two (relevant) MySQL tables. Courses_regions is used to relate courses to regions.

 

Regions{id, name, parent_id}

 

Courses_regions{id, region_id, course_id}

 

The data that I want to display on my frontend is a list of Region, sub-region and number of items (courses) related to the regions, as shown below:

 

Parent_region_name_a (5)

 

 Child_region_name_a (2)

 

 Child_region_name_b (3)

 

Parent_region_name_b (8)

 

 Child_region_name_a (5)

 

 Child_region_name_b (3)

 

The following query gives me the result I want, but without the count:

 

SELECT

 

t1.name AS lev1_name,

 

t1.id AS lev1_id,

 

t2.name AS lev2_name,

 

t2.id AS lev2_id

 

 FROM regions AS t1

 

 LEFT  JOIN regions AS t2 ON t2.parent_id = t1.id

 

 WHERE t2.name IS  NOT  NULL

 

 ORDER  BY t1.name

 

The result is:

 

lev1_name lev1_id lev2_name lev2_id

 

-----------------------  -------  ---------------  ---------

 

Blekinge län 3Olofström 188

 

Blekinge län 3Karlshamn 191

 

Blekinge län 3Sölvesborg 192

 

Dalarnas län 4Vansbro 319

 

Test län 5Gagnef 321

 

Test län 5Leksand 322

 

Test län 5Rättvik 323

 

This allows me to display the parent and sub regions with one query, but I can’t figure out how to add separate Count for level 1 and level 2.

 

Here are the tables:

Regions:

 

id name parent_id

 

3 Blekinge 1

 

4 Dalarnas 2

 

5 Test 6

 

188 Olofstrom  3

 

191 Karlshamn 3

 

192 Solvesborg 3

 

319 Vansbro 4

 

321 Gagnef 5

 

322 Leksand 5

 

323 Rattvik 5

 

1 T1

 

2 T2

 

Course:

 

id courseName

 

1 english

 

2 Chinese

 

3 Karlshamn

 

4 Vansbro

 

5 Blekinge Lan

 

Courses_regions:

 

id region_id  course_id

 

1 3 4

 

2 5 3

 

3 3 5

 

4 4 2

 

5 188 4

 

6 4 1

 

【Answer】

To write data of different levels into one data set, SQL will get data of every level and then UNION the levels while set a specified order for outputting the levels of data. That makes lengthy and unintelligible code. Your question can be handled in SPL (Structured Process Language) with intuitive and easy to understand code:

	A
1	$select ts.id id,ts.name   name,ts.parent_ID parentID,tp.name as parentName, cr.course_ID   courseID,c.courseName courseName from regions ts left join regions tp on   tp.id=ts.parent_ID left join courses_regions cr on cr.region_ID=ts.id join   course c on c.id=cr.course_id where ts.parent_ID is not null
2	=A1.group(parentID)
3	=A2.conj([~.parentID,parentName,~.count(),1]|~.group(id).conj([id,name,~.count(),2])|~.group@s(id).conj([courseID,courseName,null,3]))
4	=create(ID,Name,Count,Levl).record(A3)

A1: Associate the three tables with a JOIN.

A2: Group records by parentID.

A3: Concatenate sequences to generate records in the desired format.

A4: Create an empty table sequence and populate A3’s member to it.

esProc is designed to process structured data and an esProc SPL script can integrate with a reporting tool or a Java application through JDBC interface. That makes it an ideal tool to handle this type of computation. For more information, see How to Call an SPL Script in Java.