I have two tables, characteristics
and students
.
characteristics
id name value
---- ------ -------
1 country england
2 country brazil
3 games football
4 games baseball
5 country india
. . .
. . .
students
first_name age character_values
------------ ----- ------------------
Jason 12 [1,4]
Mark 14 [1,3]
Kunal 10 [5,3]
. . .
. . .
. . .
The characteristics
table has three columns where the id
column is an auto-increment field. The name
denotes the name of a specific characteristic and the value
indicates the respective information about the characteristic.
The students
table contains detail about each student. The character_value
field in the students
table is a jsonb
field. Each element in the jsonb array is an ID that points to the appropriate characteristic ID in the characteristics
table.
(for example, if a student is from the country of india then the ID 5 is appended to character_values
jsonb array against the respective student's name)
Assuming that each student can only belong to one country, how do I select a list of students while ordering the result by the name of the country they belong to?