This is more like a SQL question, but I have spent so much time looking for the answer that I was wondering if you could help.
I have two tables
1st Table: Languages
------------------------------------------
ID | Language | Difficulty
-----------------------------------------
1 | English | Moderate
2 | German | Hard
3 | Spanish | Hard
4 | Chinese | Very Hard
2nd Table: Students
---------------------------------
Student | SpkLanguages
----------------------------------
Mark | English
Joe | English
Lucy | Spanish, English
John | English, German, Spanish
I am trying to generate a Query Table where I can get how many times a Language from the
Languages Table, appears in the
Students Table, the desired result would be:
---------------------
Language | Times
--------------------
English | 4
German | 1
Spanish | 2
Chinese | 0
Please note that SpkLanguages in the Students Table are separated by commas.
I have tried:
SELECT
"Students"."SpkLanguages" AS Language, COUNT("Students"."SpkLanguages") AS "Times"
FROM "Students", "Languages"
WHERE
"Students"."SpkLanguages" LIKE ('%' + "Languages"."Language" + '%')
GROUP BY "Students"."SpkLanguages"
but I am getting
---------------------
Language | Times
--------------------
English | 1
English | 1
Spanish, English | 2
English, German, Spanish | 3
I am sure it is very simple, but I just can't find the way
Thanks a lot in advance
Cesar