Page 1 of 1

[Solved] How does one query a multline column?

PostPosted: Wed Aug 21, 2019 11:59 pm
by 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

Re: How does one query a multline column?

PostPosted: Fri Aug 23, 2019 12:12 am
by 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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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 54 times

Re: How does one query a multline column?

PostPosted: Fri Aug 23, 2019 1:24 am
by Sliderule
Code: Select all   Expand viewCollapse view
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.

[Solved]Re: How does one query a multline column?

PostPosted: Fri Aug 23, 2019 6:52 am
by gkick
Thanks Uncle Donald, thanks Sliderule, rolling up my sleeves right now