[Solved] How does one query a multline column?

[gkick]

Hi all,

Scenario _ tblLanguage has single or multiple line language entries aka "German" or other row may have "German English Spanish Japanese". Is it possible to have an aggregate query which counts contacts by language as a normal , since the standard
select query like SELECT "Language", COUNT( "Language" ) "Count" FROM "ViewContactsAll" "ViewContactsAll" GROUP BY "Language" will treat each multilingo as one row?

Thanks

[UnklDonald418]

In Chapter 2 of his book "SQL Antipatterns", Bill Karwin calls your dilemma as "Jay Walking".
To avoid an intersection table someone has opted represent a many to many relationship as a list of values in a single field. The result is that queries on that field can range from very difficult to impossible.
The solution is to normalize the database.
That requires a table of languages

Code: Select all

CREATE TABLE  "Languages"(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
"Language" VARCHAR(30),
CONSTRAINT "IDX_LANG" UNIQUE ( "Language" )
);

If there aren't too many languages then typing them into the Languages table will probably be the simplest option.
I made an assumption that there is a table named "Contacts" that includes a Primary Key field "ID" because a View will not work here.
Then an intersection table would be

Code: Select all

CREATE TABLE  "ContactXLanguage"(
"ContactID" INTEGER,
"LanguageID" INTEGER,
CONSTRAINT "FK_CONT" FOREIGN KEY ("ContactID") REFERENCES "Contacts" ("ID"),
CONSTRAINT "FK_LANG" FOREIGN KEY ("LanguageID") REFERENCES "Languages" ("ID"),
CONSTRAINT "PK_CTLG" PRIMARY KEY ("ContactID","LanguageID" )
);

If the language table is populated then executing the following SQL command should populate the intersection table

Code: Select all

INSERT INTO "ContactXLanguage"( "ContactID", "LanguageID")
(SELECT "Contacts"."ID", "Languages"."ID" FROM "Contacts", "Languages" WHERE LOCATE( "Languages"."Language", "Contacts"."Language" ) > 0 
AND LOCATE( CHAR( 32 ), "Language" ) >= 0);

Finally, the query you were after is

Code: Select all

SELECT "Language", COUNT( "Language" ) "Count" FROM "ContactXLanguage", "Languages" AS "Languages" WHERE "ContactXLanguage"."LanguageID" = "Languages"."ID"
GROUP BY "Language" ORDER BY "Language"

Here is the demonstration database where I tested everything.

Demo41_Normalization.odb

(12.46 KiB) Downloaded 185 times

[Sliderule]

Code: Select all

SELECT 
   "ViewContactsAll"."Language",
   -- Calculation Below Assumes ONE Space Between Each Language
   CASE WHEN LENGTH(COALESCE("ViewContactsAll"."Language", SPACE(0))) = 0 THEN 0 
        ELSE 
             LENGTH("ViewContactsAll"."Language") - LENGTH(REPLACE("ViewContactsAll"."Language", SPACE(1), SPACE(0))) + 1 
   END AS "Count"
FROM "ViewContactsAll" AS "ViewContactsAll"

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[gkick]

Thanks Uncle Donald, thanks Sliderule, rolling up my sleeves right now