CODEWITHSUNDEEP
mysqldatabaselaravelduplicates
Soul Coder
Soul Coder

Reputation: 804

How to return all duplicates rows in same columns in laravel 5.4?

I want to get all the list of jobseekers which is the same fullname and country, in Jobseekers Table,

**id** |fullname    |country    |phone_number

1      |John        | singapore |0988884434

2      |john        | singapore |0933333333

3      |Michael     |Malaysia   |0888888888

4      |Smith       |Dubai      |082388888888

5      |Smith       |Dubai      |03939494944

what am I expect here is,

john  |singapore
john  |singapore
Smith |Dubai
Smith |Dubai

this is what i tried here,

 $duplicates = DB::table('jobseekers')
                ->select(array('jobseekers.fullname','jobseekers.country', DB::raw('COUNT('*')'))
                ->groupBy('jobseekers.fullname','jobseekers.country')
            ->having(DB::raw('COUNT('*')'))
                ->get();
       var_dump($duplicates);

Any help or advice you have would be greatly appreciated.

Upvotes: 3

Views: 4529

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31802

Try this one:

$duplicates = DB::table('jobseekers')
    ->select('fullname','country', DB::raw('COUNT(*) as `count`'))
    ->groupBy('fullname', 'country')
    ->having('count', '>', 1)
    ->get();

Upvotes: 7

Darshan Mehta
Darshan Mehta

Reputation: 30819

You need to use INNER query to get the rows, e.g.:

SELECT fullname, country
FROM jobseekers
WHERE fullname IN (
    SELECT fullname
    FROM jobseekers
    GROUP BY fullname
    HAVING COUNT(*) > 1
);

Upvotes: 2

Related Questions