CODEWITHSUNDEEP
mysqlsql
Leandro Garcia
Leandro Garcia

Reputation: 3228

combine result and separate by comma

I have this query:

SELECT
  tud.detail_company_name AS company_name,
  tud.detail_country AS company_country,
  trv.review_title AS review_title
FROM users_detail tud
INNER JOIN users_auth tua
  ON tud.user_id = tua.user_id
LEFT JOIN reviews trv
  ON tud.user_id = trv.review_company_id
WHERE
  tua.auth_user_type = "company" OR tua.auth_user_type = "guestcompany"

The query works fine, and it return results like this:

Array
(
    [0] => Array
        (
            [company_name] => The Coffee Brewery
            [company_country] => US
            [review_title] => 
        )
    [1] => Array
        (
            [company_name] => Crea Nail Art Studio
            [company_country] => SG
            [review_title] => 
        )
    [2] => Array
        (
            [company_name] => Hello Mall
            [company_country] => JP
            [review_title] => Fake goods!
        )
    [3] => Array
        (
            [company_name] => Hello Mall
            [company_country] => JP
            [review_title] => Never buy in there!
        )
)

As you noticed array key 2 & 3 has the same company_name but different review_title. Is there any possibility that it must be joined in one result and separated by comma? Say for example this result:

Array
(
    [0] => Array
        (
            [company_name] => The Coffee Brewery
            [company_country] => US
            [review_title] => 
        )
    [1] => Array
        (
            [company_name] => Crea Nail Art Studio
            [company_country] => SG
            [review_title] => 
        )
    [2] => Array
        (
            [company_name] => Hello Mall
            [company_country] => JP
            [review_title] => Fake goods!, Never buy in there!
        )
)

EDIT

What if the response is this:

Array
(
    [0] => Array
        (
            [company_name] => The Coffee Brewery
            [company_country] => US
            [review_title] => 
            [review_approved] =>
        )
    [1] => Array
        (
            [company_name] => Crea Nail Art Studio
            [company_country] => SG
            [review_title] => 
            [review_approved] =>
        )
    [2] => Array
        (
            [company_name] => Hello Mall
            [company_country] => JP
            [review_title] => Fake goods!
            [review_approved] => 1
        )
    [3] => Array
        (
            [company_name] => Hello Mall
            [company_country] => JP
            [review_title] => Never buy in there!
            [review_approved] => 0
        )
)

How can I not include 0 review_approved on the review_title GROUP_CONCAT? So it will output like this?

Array
(
    [0] => Array
        (
            [company_name] => The Coffee Brewery
            [company_country] => US
            [review_title] => 
        )
    [1] => Array
        (
            [company_name] => Crea Nail Art Studio
            [company_country] => SG
            [review_title] => 
        )
    [2] => Array
        (
            [company_name] => Hello Mall
            [company_country] => JP
            [review_title] => Fake goods!
        )
)

Upvotes: 0

Views: 244

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Yes. Use GROUP_CONCAT():

SELECT
  tud.detail_company_name AS company_name,
  tud.detail_country AS company_country,
  GROUP_CONCAT(trv.review_title SEPARATOR ',') AS review_titles
FROM users_detail tud
INNER JOIN users_auth tua
  ON tud.user_id = tua.user_id
LEFT JOIN reviews trv
  ON tud.user_id = trv.review_company_id
WHERE
  tua.auth_user_type = :company OR tua.auth_user_type = :guestcompany
GROUP BY 
    tud.detail_company_name,
    tud.detail_country;

Upvotes: 2

Related Questions