System: Windows 10, LO 6.3.4.2 (x64), Firebird
I have two tables, CLIENT and NATIONALITY, where the structure is:
CLIENT
...
Nationality_ID (FK)
NATIONALITY
Nationality_ID (PK)
Nationality (string description)
I want to count the number of occurrences of each Nationality_ID in the CLIENT table, and also to pull out the Nationality (string description) for each Nationality_ID from the NATIONALITY table.
This query works:
SELECT
c."Nationality_ID",
COUNT(*)
FROM
"Client" c
GROUP BY
c."Nationality_ID";
Results:
,1,
2,1,
4,1,
5,2,
11,1,
13,1,
14,1,
But as soon as I include an INNER JOIN to get at “Nationality”.“Nationality”, I get an error. The error I get is different, depending on whether I run the query in Tools>SQL>Execute SQL Statement or in Query Design, which is rather confusing. AFAIAA, my syntax conforms with the examples given in ‘Firebird_Language_Reference_25EN.pdf’.
In ‘Execute SQL Statement’:
SELECT
c."Nationality_ID",
n."Nationality",
COUNT(*)
FROM
"Client" c
INNER JOIN
"Nationality" n ON n.Nationality_ID = c.Nationality_ID
GROUP BY
c."Nationality_ID";
Error generated:
*SQL error code = -104
*Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
caused by
'isc_dsql_prepare'
In Query Design:
SELECT
c."Nationality_ID",
n."Nationality",
COUNT(*)
FROM
"Client" c
INNER JOIN
"Nationality" n ON n.Nationality_ID = c.Nationality_ID
GROUP BY
c."Nationality_ID";
Error generated:
*SQL error code = -206
*Column unknown
*C.NATIONALITY_ID
*At line 10, column 5
caused by
'isc_dsql_prepare'
I still get errors if I dispense with table aliasing and use the full table names. And BTW, for those other newbies like me, Firebird SQL seems to demand double quotes around table and column names, unlike the various examples given on w3schools and other SQL tutorial sites.
Can anyone please explain what my problem is?