Combine multiple rows of three tables into one row in MySQL

Question

I read the question Combine multiple rows into one row MySQL that shows how to make out of several rows of a SELECT statement a result with a single line. The SQL Fiddle with Demo also runs fine. In that question, there are two tables: t1 and t2. My problem has three tables, which I have simplified for this question:

Table languages:

ID              INT(11)         NOT NULL    PRIMARY_KEY
name            VARCHAR(255)    NOT NULL    UNIQUE

Table languages_have_persons:

Languages_ID    INT(11)         NOT NULL    PRIMARY_KEY
Persons_ID  INT(11)             NOT NULL    

Table persons:

ID              INT(11)         NOT NULL    PRIMARY_KEY
firstName       VARCHAR(255)    NOT NULL
lastName        VARCHAR(255)    NOT NULL

What I want to do is to combine these tables similarly, but I fail to do so for several hours now. Here is my last try:

SELECT p1.ID
    , p1.firstName
    , p1.lastName
    CONCAT_WS(', '
        , l2de.name
        , l2en.name
    )
    FROM
        languages_have_persons AS lp, persons AS p1
    LEFT JOIN languages AS l2de ON
        l2de.ID = 4 -- German
    LEFT JOIN languages AS l2en ON
        l2en.ID = 5 -- English
WHERE p1.ID = lp.Persons_ID
ORDER BY lastName, firstName;

The error message reads:

Fehler
SQL-Befehl: Dokumentation


SELECT p1.ID AS ID
    , p1.firstName AS firstName
    , p1.lastName AS lastName
    CONCAT_WS(', '
        , l2de.name
        , l2en.name
    )
    FROM
        languages_have_persons AS lp, persons AS p1
    LEFT JOIN languages AS l2de ON
        l2de.ID = 4     LEFT JOIN languages AS l2en ON
        l2en.ID = 5 WHERE p1.ID = lp.Persons_ID
ORDER BY lastName, firstName LIMIT 0, 25
MySQL meldet: Dokumentation

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch
nachschlagen bei '(', '
        , l2de.name
        , l2en.name
    )
    FROM
        languages_have_persons AS lp, ' in Zeile 4

I also tried other ways, but none had the correct syntax. What I want as result is as follows:

Table language:

ID    name
4     German
5     English

Table language_have_persons:

Languages_ID    Persons_ID    
    4                2
    5                1
    5                2

Table persons:

ID    firstName    lastName
1     Joe           Frazier
2     Arnold        Schwarzenegger

What I expect from the correct SELECT statement is something like

firstName    lastName           CONCAT_WS(…
Joe           Frazier           English
Arnold        Schwarzenegger    German, English

Do you see the error in the SQL syntax above? If not, do you know how to solve it?

Thank you for your help!

Answer 1

SELECT p1.ID
    , p1.firstName
    , p1.lastName,<--------------------I had made change at this line
    CONCAT_WS(', '
        , l2de.name
        , l2en.name
    )
    FROM
        languages_have_persons AS lp, persons AS p1
    LEFT JOIN languages AS l2de ON
        l2de.ID = 4 -- German
    LEFT JOIN languages AS l2en ON
        l2en.ID = 5 -- English
WHERE p1.ID = lp.Persons_ID
ORDER BY lastName, firstName;

Or, for your further query you can try below.

SELECT p1.ID
    , p1.firstName
    , p1.lastName,
    l2de.name
    FROM
    persons p1
    INNER JOIN languages_have_persons lp on p1.ID=lp.Persons_ID 
    INNER JOIN language l2de on l2de.ID=lp.Languages_ID 
    WHERE l2de.ID  IN(4,5)
    ORDER BY lastName, firstName;

You can try above query.

Answer 2

On another site, I had another discussion, where the colleague found the solution, which is

SELECT DISTINCT pl.ID
    , firstName
    , lastName
    , languages
FROM persons p
    , ( SELECT
        lp.Persons_ID AS ID,
        GROUP_CONCAT(DISTINCT l.name) AS languages
    FROM languages l
        , languages_have_persons lp
    WHERE l.id = lp.Languages_ID
    GROUP BY lp.Persons_ID
    ) AS pl
WHERE p.id = pl.id
ORDER BY p.id;

So, the question is ANSWERED PROPERLY.