asked on

Trying to get a field in multiple rows into one row in MySQL

Hello!

I have three tables called languages, languages_have_persons, and persons. The contents are a follows:

Table languages:

	ID	name
	1	Arabic
	4	German
	5	English

Open in new window

Table languages_have_persons:

	Languages_ID	Persons_ID
		4				1
		4				2
		5				2

Open in new window

Table persons:

	ID	firstName	lastName
	1	Joe		Fraizer
	2	Arnold		Schwarzenegger

Open in new window

What I want is a result of the form

	ID	firstName	lastName		…
	1	Joe		Fraizer			English
	2	Arnold		Schwarzenegger	German, English

Open in new window

The SQL statement for MySQL I have written is

SELECT DISTINCT p1.ID
    , p1.firstName
    , p1.lastName
    , CONCAT_WS(', '
        , l2ar.name
        , l2de.name
        , l2en.name
    )
    FROM
        languages_have_persons AS lp, persons AS p1
    LEFT JOIN languages AS l2ar ON
        l2ar.ID = 1 -- Arabic
    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;

Open in new window

What I get is

	ID	firstName	lastName		…
	1	Joe		Fraizer			Arabic, German, English
	2	Arnold		Schwarzenegger	Arabic, German, English

Open in new window

I tried also the command

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

Open in new window

which results in

	ID	firstName	lastName		…
	1	Joe		Fraizer			German, German
	2	Arnold		Schwarzenegger	English, English

Open in new window

Can you see what I have done wrong? Also, the DISTINCT statement is currently required, as I get twice the number of rows otherwise. Do you know what causes that? I lost several hours on this issue, but cannot see where I am doing wrong.

Thank you for your replies!

Tomas Helgi Johannsson

Hi!

This should do what you are trying to do . :)
I simplified the problem it a little bit. ;)

select p.*,pl.* 
from persons p, (
select p.persons_id as id, group_concat(l.name) as names
from languages_have_persons p, languages l
where l.id = p.languages_id
group by l.id ) as pl
where p.id = pl.id
order by p.id

Open in new window

Regards,
Tomas Helgi

Bill Prew

Nice Tomas.

Not much I can add to that, and I'm sure you already know how, but if you don't want the second ID in the results set easy enough to remove that as below (not for any points please).

SELECT p.*,
       pl.names
FROM   persons p,
       (SELECT p.persons_id         AS id,
               Group_concat(l.NAME) AS names
        FROM   languages_have_persons p,
               languages l
        WHERE  l.id = p.languages_id
        GROUP  BY l.id) AS pl
WHERE  p.id = pl.id
ORDER  BY p.id;

Open in new window

»bp

Ahmet Ekrem SABAN

ASKER

Hello, and thank you Tomas Helgi Johannsson and Bill Prew!

With your (modified) query

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

Open in new window

I get the following result:

ID		firstName	lastName	names	
12562	Ahmet		Saban		Arabisch	
12562	Ahmet		Saban		Norwegisch	
12562	Ahmet		Saban		Schwedisch	
27931	DeLang		Sea		Englisch,Englisch,Englisch,Englisch,Englisch,Engli...	
35197	Bojan		Mikis		Kroatisch	
41022	Nuray		Gümüser-Köse	Türkisch	
45985	Johannes	Mayer		Deutsch,Deutsch,Deutsch,Deutsch,Deutsch,Deutsch,De...	
46021	Mariam		Mosham		Persisch,Persisch

Open in new window

So, the query still has problems:

Some persons are enlisted more than once

Persons who are enlisted once may have the same language repeated

Also, the table contents of *languages_have_persons* is such that everybody does know German & English. So, rows without these two languages seem to be wrong either

Tomas Helgi Johannsson

Hi!

Hmmm, can you post the structure (ddl) of the tables ?
This outcome of your sql indicates that the id columns in the tables or the data are not 100% compatible.
Also in the table languages_have_persons there seems to be some multiple rows of the same data. You can eliminate that by putting DISTINCT in the GROUP_CONCAT like this

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
        l.id
	) AS pl
WHERE
    p.id = pl.id
ORDER BY
    p.id;

Open in new window

Regards,
Tomas Helgi

Ahmet Ekrem SABAN

ASKER

Thank you very much for your reply, Tomas! You were right: The table format was not identical, but I modified the diverging ones. Here you go…
Table languages:

	#	Name	Typ		Collation	Attribute	Null	Standard	Comments	Extra	
	1	ID	int(11)						no	none				AUTO_INCREMENT	 PRIMARY KEY
	2	name	varchar(45)	utf8_general_ci			yes	NULL

Open in new window

Table languages_have_persons:

	#	Name	Typ		Collation	Attributes	Null	Standard	Comments	Extra
	1	Languages_ID	int(11)					no	none				AUTO_INCREMENT
	2	Persons_ID	int(11)					yes	NULL

Open in new window

Table persons:

	#	Name		Typ		Collation	Attributes	Null	Standard	Comments	Extra
	1	ID		int(11)						no	none	 				PRIMARY KEY
	2	username	varchar(255)	utf8_general_ci			no	none
	3	password	varchar(255)	utf8_general_ci			no	none
	4	titleID		int(11)						yes	NULL
	5	firstName	varchar(255)	utf8_general_ci			yes	NULL
	6	lastName	varchar(255)	utf8_general_ci			yes	NULL
	7	eMail		varchar(50)	utf8_general_ci			yes	NULL
	8	addressID	int(11)						yes	NULL
	9	address2ID	int(11)						yes	NULL
	10	address3ID	int(11)						yes	NULL
	11	description	varchar(255)	utf8_general_ci			yes	NULL
	12	phone1		varchar(50)	utf8_general_ci			yes	NULL
	13	phone2		varchar(50)	utf8_general_ci			yes	NULL
	14	status		int(11)						no	none
	15	publicMail	varchar(255)	utf8_general_ci			no	none
	16	advisorID	int(11)						no	none

Open in new window

Ahmet Ekrem SABAN

ASKER

Here is the result of your updated query:

The problems with the query:

The known languages should appear in one column, separated with commas. In other words, one entry per person is required

All persons know German & English, so this should be visible

Tomas Helgi Johannsson

Hi!

I see some problem in the languages_have_persons table. The language_id is with AUTO_INCREMENT when it should only have a value from the language table. This makes the data for like Ahmet multiple.
What do you get if you do this select

SELECT
        lp.*,l.*
    FROM
        languages l
        , languages_have_persons lp
    WHERE
        l.id = lp.Languages_ID
        and lp.Personsid = 12562

Open in new window

and this

select p.* from persons where firstname = 'Ahmet' and lastname = 'Saban'

Open in new window

Regards,
Tomas Helgi

Ahmet Ekrem SABAN

ASKER

Thank you very much!!! :-D I removed the AUTO_INCREMENT from languages_have_persons now.

The first select

SELECT
        lp.*,l.*
    FROM
        languages l
        , languages_have_persons lp
    WHERE
        l.id = lp.Languages_ID
        and lp.Persons_ID = 12562

Open in new window

returns

The second query,

SELECT
    p.*
FROM
    persons p
WHERE
    firstName = 'Ahmet' AND lastName = 'Saban'

Open in new window

returns a record with the data of the person 'Saban'.

Ahmet Ekrem SABAN

ASKER

Take the following data:

Table languages2;

ID	name
1	Arabisch
2	Bosnisch
3	Chinesisch
4	Deutsch
5	Englisch

Open in new window

Table languages_have_persons2;

Languages_ID	Persons_ID	
	1		3	
	5		1	
	4		2	
	5		2

Open in new window

Table persons2;

ID	firstName	lastName
1	Joe		Fraizer
2	Arnold		Schwarzenegger
3	Muhammad	Ziya

Open in new window

The query

SELECT p.ID, firstName, lastName, name AS language
FROM languages l
	, languages_have_persons2 lp
	, persons2 p
WHERE l.id = lp.Languages_ID
  AND lp.Persons_ID = p.ID
ORDER BY lastName, firstName, language;

Open in new window

returns the following:

If we could modify the query such that each person a row only, and the languages are comma-separated & sorted in another column, we are done.

Tomas Helgi Johannsson

Hi!

Now with the new tables try this query again.

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

Open in new window

Regards,
Tomas Helgi

Ahmet Ekrem SABAN

ASKER

Here is the result: