Search code examples
sqlsqlitejoincasesql-max

Reordering the results of an SQL query using SQLite

I have a SQLite database that models Sanskrit nouns and has tables like this: (Sorry if it is very lengthy. I've tried to cut things down to the minimum necessary to understand this problem.)

numbers:

id number
1 singular
2 dual
3 plural

cases:

id case
1 nominative
2 accusative
3 instrumental
4 dative
5 ablative
6 genitive
7 locative
8 vocative

nouns:

id name
1 rAma

forms:

id form noun
1 rAmaH 1
2 rAmau 1
3 rAmAH 1
4 rAmam 1
5 rAmAN 1
6 rAmENa 1
7 rAmAbhyAm 1
8 rAmaiH 1
9 rAmAya 1
10 rAmebhyaH 1
11 rAmAt 1
12 rAmasya 1
13 ramayoH 1
14 rAmANAm 1
15 rAme 1
16 rAmeShu 1
17 rAma 1
  • noun is a foreign key which references nouns(id)

nounforms:

id form case number noun
1 1 1 1 1
2 2 1 2 1
3 3 1 3 1
4 4 2 1 1
5 2 2 2 1
6 5 2 3 1
7 6 3 1 1
8 7 3 2 1
9 8 3 3 1
10 9 4 1 1
11 7 4 2 1
12 10 4 3 1
13 11 5 1 1
14 7 5 2 1
15 10 5 3 1
16 12 6 1 1
17 13 6 2 1
18 14 6 3 1
19 15 7 1 1
20 13 7 2 1
21 16 7 3 1
22 17 8 1 1
23 2 8 2 1
24 3 8 3 1
  • form is a foreign key which references forms(id)
  • case is a foreign key which references cases(id)
  • number is a foreign key which references numbers(id)
  • noun is a foreign key which references nouns(id)

I can get all the declensions of the noun rAma with this SQL query:

SELECT forms.form FROM forms JOIN nouns,nounforms
    WHERE forms.id = nounforms.form
        AND nounforms.noun = nouns.id
        AND noun.name = "rAma"
    GROUP BY nounforms.case, nounforms.number;

and that returns the whole noun perfectly in 24 rows:

form
rAmaH
rAmau
rAmAH
rAmam
rAmau
rAmAN
rAmENa
rAmAbhyAm
rAmaiH
rAmAya
rAmAbhyAm
rAmebhyaH
rAmAt
rAmAbhyAm
rAmebhyaH
rAmasya
ramayoH
rAmANAm
rAme
ramayoH
rAmeShu
rAma
rAmau
rAmAH

So far so good. But what I would really like is something like this:

singular dual plural
rAmaH rAmau rAmAH
rAmam rAmau rAmAN
rAmENa rAmAbhyAm rAmaiH
rAmAya rAmAbhyAm rAmebhyaH
rAmAt rAmAbhyAm rAmebhyaH
rAmasya ramayoH rAmANAm
rAme ramayoH rAmeShu
rAma rAmau rAmAH

i.e. 8 rows for each case with 3 columns for each number. The problem is my SQL knowledge is not quite enough to get me there. I think what I want is a view or a virtual table. Is that right? Also once that is solved, I would like to parametrize the query so I can use it for nouns other than rAma but SQLite does not I believe support stored procedures. Is that right? If so, what is the workaround?

Btw, I am aware that I can do the reordering in my application. In fact, that is what I am doing now but I would like to keep as much centralized in the database as possible so I can port to other languages/environments.

Can anyone help?

Solution

  • You need conditional aggregation:

    SELECT MAX(CASE WHEN nf.number = 1 THEN f.form END) singular,
       MAX(CASE WHEN nf.number = 2 THEN f.form END) dual,
       MAX(CASE WHEN nf.number = 3 THEN f.form END) plural
FROM forms f 
JOIN nouns n ON n.id = f.noun
JOIN nounforms nf ON f.id = nf.form AND nf.noun = n.id
WHERE n.name = ?
GROUP BY nf.`case`;

    Replace the placeholder ? with the noun that you want.

    Also, always use proper joins with ON clauses and aliases for the tables to make the code shorter and more readable.

    See the demo.

    As you already know, SQLite does not support stored procedures or functions, so probably the best way to use this query is as it is in your app with the the placeholder ? in a prepared statement and pass the value of the noun as a parameter.