[Solved] How does one query a multline column?

Creating tables and queries

[Solved] How does one query a multline column?

Postby gkick » Wed Aug 21, 2019 11:59 pm

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
Last edited by gkick on Sun Aug 25, 2019 9:47 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 268
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How does one query a multline column?

Postby UnklDonald418 » Fri Aug 23, 2019 12:12 am

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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1337
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: How does one query a multline column?

Postby Sliderule » Fri Aug 23, 2019 1:24 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1244
Joined: Thu Nov 29, 2007 9:46 am

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

Postby gkick » Fri Aug 23, 2019 6:52 am

Thanks Uncle Donald, thanks Sliderule, rolling up my sleeves right now
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
 
Posts: 268
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest