CODEWITHSUNDEEP
phpmysqllaraveljoin
hansi silva
hansi silva

Reputation: 99

GROUP concat with mysql join tables

I need to Join 2 tables for get data and im using SELECT GROUP CONCAT as well. I works fine when i joined 1 table but when i trying to join three tables values gettings repeat. please advice .

below is my query 

     $data = DB::table('festivals')
    ->join('namedesgs', 'festivals.id', '=', 'namedesgs.festival')
     ->join('vehicles', 'festivals.id', '=', 'vehicles.festival')
    ->select(DB::raw(" GROUP_CONCAT(vehicles.role SEPARATOR '\n') as role,GROUP_CONCAT(vehicles.size SEPARATOR '\n') as size,festivals.id,festivals.ref_no as ref_no, festivals.camping,festivals.tour_mgr_name,festivals.email,festivals.mobile,festivals.name_address, GROUP_CONCAT(namedesgs.name SEPARATOR '\n') as names,GROUP_CONCAT(namedesgs.designation SEPARATOR '\n') as designations"))
    ->groupBy('festivals.id')
    ->get();

Upvotes: 0

Views: 268

Answers (2)

codisfy
codisfy

Reputation: 2183

Seems like one of your tables has multiple entries for the same festival.id. Which leads to you have multiple rows for the same festival.id and eventually you having repeated items in your group_concat.

So first check if what I have mentioned above should really happen.

If it should then try adding DISTINCT to your group concat: 

 $data = DB::table('festivals')
    ->join('namedesgs', 'festivals.id', '=', 'namedesgs.festival')
     ->join('vehicles', 'festivals.id', '=', 'vehicles.festival')
    ->select(DB::raw(" GROUP_CONCAT(DISTINCT vehicles.role SEPARATOR '\n') as role,GROUP_CONCAT(DISTINCT vehicles.size SEPARATOR '\n') as size,festivals.id,festivals.ref_no as ref_no, festivals.camping,festivals.tour_mgr_name,festivals.email,festivals.mobile,festivals.name_address, GROUP_CONCAT(DISTINCT namedesgs.name SEPARATOR '\n') as names,GROUP_CONCAT(DISTINCT namedesgs.designation SEPARATOR '\n') as designations"))
    ->groupBy('festivals.id')
    ->get();

Upvotes: 1

EddyTheDove
EddyTheDove

Reputation: 13259

Give it a try with leftjoin

$data = DB::table('festivals')
->leftjoin('namedesgs', 'festivals.id', '=', 'namedesgs.festival')
->leftjoin('vehicles', 'festivals.id', '=', 'vehicles.festival')

If it still doesn't give the result you expect, read more about different joins here and the result they will give you: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Upvotes: 0

Related Questions