CODEWITHSUNDEEP
mysqlsql
Jeba
Jeba

Reputation: 868

GROUP_CONCAT issue with string

SELECT (SELECT GROUP_CONCAT(companyName)FROM company WHERE id IN (27,38)) AS companyName, t.* 
FROM Test t

it return output like this,

>Apple, Sony

But if I pass the id as full string, 

SELECT (SELECT GROUP_CONCAT(companyName) 
        FROM company WHERE id IN ("27,38")) AS companyName, t.* 
FROM Test t

So it return first company name only, so output like this

>Apple

I want to retrieve the data like this Apple, Sony.(Like first query), How can I achieve this one?

Sample Table Structure below

company Table

    --------------------
    ID      companyName
    -------------------
    27         Apple
    28         Sony
    .         .
    .         .

Upvotes: 0

Views: 87

Answers (3)

Isravel
Isravel

Reputation: 46

You are passing 27 and 28 as single string so MySQL consider the first number which is 27, that's why it's returning apple. If you want return Apple and Sony you have to split the string.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

I saw your comments it looks like you are storing company ids for Trailers as a comma separated string which is against of normalization, Instead you can add new junction table which hold the reference of both tables company and trailers like 

trailer
=======
id name
1  some name 


company
id      name
-------------------
1         Apple
2         Sony

trailer_companies (junction table)
trailer_id company_id
---------------------
1          27
1          38

Now you can query records by using joins 

select t.*,group_concat(c.name) companyName
from trailer t
join trailer_companies tc on (t.id = tc.trailer_id)
join company c on (tc.company_id = c.id)
group by t.id

DEMO

If you want to continue with your current design which is strongly not advisable because it will be hard to optimize and not fit for scaled apps

SELECT 
    (SELECT 
    GROUP_CONCAT(companyName SEPARATOR ',') 
    FROM company 
    WHERE  FIND_IN_SET(id,t.companyIds) > 0
    ) AS companyName, t.* 
FROM Trailers t

Upvotes: 0

Ravi
Ravi

Reputation: 31407

You need to use

SELECT group_concat(companyName separator ',')
FROM company WHERE id IN ("27,38");

Upvotes: 0

Related Questions