SQL Query - Join one to many values from another table / creating same column twice / look up values for the current table

Question

Help needed - not been able to work out a simple query to perform the following task:

Table 1 - Weblink

ID   Weblink     Language1_ID   Language2_ID
--   ----------  ------------   ------------
1    google.com  1              2
2    amazon.cn   (Null)         3
3    yahoo.pl    5              1
4    webex.jp    4              (Null)

Table 2 - Language

ID   Language
--   -----------
1    English
2    French
3    Chinese
4    Japanese
5    Polish

The end result I'd like to see:

ID   Weblink     Language
--   ----------  ---------------
1    google.com  English, French
2    amazon.cn   Chinese
3    yahoo.pl    Polish, English
4    webex.jp    Japanese

I'd like to populate all the languages into the table and merge multiple values by comma if possible.

Possible duplicate of [Ms Access Query: Concatenating Rows through a query](http://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query) — Gurwinder Singh, Mar 22 '17 at 17:20

score 0 · Accepted Answer · answered Mar 22 '17 at 19:09

0

the simple solution. (sorry for giving you the full SQL but I was bored)

SELECT A.ID, A.weblink, 
   (SELECT language FROM Table2 WHERE ID = A.Language1_ID) & _
   iif(A.Language1_ID is null, "", ", ") & _
   (SELECT language FROM Table2 WHERE ID = A.Language2_ID) AS Expr1
FROM Table1 AS A;

answered Mar 22 '17 at 19:09

geeFlo

365
4
10

Many thanks geeFlo, great query - exactly what I'm looking for! With a second nested iif statement it hides the comma if only Language1 exists. iif(A.Language1_ID is null, "", iif(A.Language2_ID is null, "", ", ")) Thank you kindly for your time :-) – Vienna Mar 23 '17 at 10:10

SQL Query - Join one to many values from another table / creating same column twice / look up values for the current table

1 Answers1