SQL Query: Help with errors

[bbater]

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?

[Ratslinger]

Hello,

There are two problems. One is not following your own advice.

This is about quoting. You are missing quotes on the join statement. The quoting is necessary because of using mixed case.

Second is the Group By section. “Nationality” needs to be added.

Here is a tested statement:

SELECT
    c."Nationality_ID",
    n."Nationality",
    COUNT(*)
FROM
    "Client" c
INNER JOIN
    "Nationality" n ON n."Nationality_ID" = c."Nationality_ID"
GROUP BY
    "Nationality_ID","Nationality";

[Ratslinger]

A note about your result in the working SQL. It appears you have a record without a link to the “Nationality” table. It may be better if this was another item in that table with a description of something like “None Given”. If this is not wanted, you can still get the result with the SQL in my answer by changing INNER JOIN to LEFT JOIN.

[bbater]

Once again to my rescue, Ratslinger. So grateful. But I think I’m learning with your help, since my appeals for help are getting less frequent … aren’t they?