Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

how to get the duplicate records from mysql query

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

how to get the duplicate records from mysql query

Postby stephenrodrics » Tue Aug 13, 2013 12:48 am

how to get the duplicate records from mysql query

I have empid and week field, I want to see is there any duplicate week entry for any employee
stephenrodrics
New php-forum User
New php-forum User
 
Posts: 53
Joined: Thu Mar 14, 2013 6:10 am

Re: how to get the duplicate records from mysql query

Postby johnj » Tue Aug 13, 2013 12:52 am

Code: Select all
select * from some_table where employee_id = "$emp_id" and week_field = "$week_field"
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: how to get the duplicate records from mysql query

Postby saramaria » Wed Aug 14, 2013 4:03 am

Dear stephenrodrics,

The MySQL is more important for php developerswithout mysql knowledge you can't developed a effective website.
For finding duplicate contents use the following sql query.

mysql> SELECT *, count(*) as n
-> FROM employee12
-> group by empid
-> HAVING n>1;

or by Using the having keyword

select firstname, lastname, count(*) cnt
from author
group by firstname, lastname
having cnt > 1
order by cnt asc;

For more info visit..
http://www.valuecoders.com/hire-developers/hire-php-developers
saramaria
New php-forum User
New php-forum User
 
Posts: 8
Joined: Thu Jul 18, 2013 5:38 am

Re: how to get the duplicate records from mysql query

Postby johnj » Wed Aug 14, 2013 5:17 am

saramaria is right. We will have to GROUP the records based on a key and then use the HAVING keyword.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: how to get the duplicate records from mysql query

Postby johnj » Wed Aug 14, 2013 5:21 am

i think this will also work
Code: Select all
SELECT DISTINCT a.empid FROM `employee` a LEFT JOIN `employee` b ON a.empid = b.empid WHERE a.empid != b.empid;
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: how to get the duplicate records from mysql query

Postby stephenrodrics » Mon Aug 19, 2013 2:42 am

this does not fetch any record

SELECT DISTINCT a.empid FROM `employee` a LEFT JOIN `employee` b ON a.empid = b.empid WHERE a.empid != b.empid;

by the way I need emplyee IDS who are having duplicate entries

example I have duplicate entry for employee 202

EID 202 Week 2013-08-13
EID 202 Week 2013-08-13

I want a query which give me a list of employees which are having more than 1 entry in Same Week same date
Last edited by stephenrodrics on Mon Aug 19, 2013 8:38 am, edited 1 time in total.
stephenrodrics
New php-forum User
New php-forum User
 
Posts: 53
Joined: Thu Mar 14, 2013 6:10 am

Re: how to get the duplicate records from mysql query

Postby stephenrodrics » Mon Aug 19, 2013 8:33 am

saramaria wrote:Dear stephenrodrics,

The MySQL is more important for php developerswithout mysql knowledge you can't developed a effective website.
For finding duplicate contents use the following sql query.

mysql> SELECT *, count(*) as n
-> FROM employee12
-> group by empid
-> HAVING n>1;

or by Using the having keyword

select firstname, lastname, count(*) cnt
from author
group by firstname, lastname
having cnt > 1
order by cnt asc;

For more info visit..
http://www.valuecoders.com/hire-developers/hire-php-developers



the above query will give me a list of people whose entries count is more than 1

I need people who are having duplicate entries

suppose there is a employee 202

so in the below scenario there are 2 records in table weekly for employee 202 with same date, here employee ID and week is main field, how to find those guys ?

202 with week as 2013-08-13
202 with week as 2013-08-13
stephenrodrics
New php-forum User
New php-forum User
 
Posts: 53
Joined: Thu Mar 14, 2013 6:10 am


Return to PHP coding => General

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.