CODEWITHSUNDEEP
mysqlsql
Madryoch
Madryoch

Reputation: 85

How to get all rows of a table that share a value on a specific field?

For Example

If i have a table named students that has 4 fields id,fname,lname and dob (date of birth) How can I retrieve all students' names that share birthdays (without asking for a specific date of Birth) as well as the date they are sharing?

id int primary key not null and auto_increment
fname and lname are varchar
dob is date

id    fname     lname       dob

1.    Nick      Tailor      1993-04-27
2.    Mary      White       1987-11-13
3.    Helen     Wilde       1993-04-27
4.    George    Bishop      1981-02-29
5.    John      Walker      1981-02-29
6.    Vincent   Valentine   1981-02-29

and the result being 

fname     lname       dob

Nick      Tailor      1993-04-27
Helen     Wilde       1993-04-27
George    Bishop      1981-02-29
John      Walker      1981-02-29
Vincent   Valentine   1981-02-29

Mary isn't shown anywhere since she doesn't share dob with anyone in the table.

Upvotes: 3

Views: 83

Answers (6)

Lucifer Rodstark
Lucifer Rodstark

Reputation: 216

Solution Query :

SELECT fname as 'First Name', lname as 'Last Name',dob as 'Birthday' FROM students WHERE dob IN(SELECT dob FROM students GROUP BY dob HAVING COUNT(id) > 1) ORDER BY dob;

Upvotes: 1

AT-2017
AT-2017

Reputation: 3149

As you are using year to extract those duplicate data, here I've used a sample:

SELECT Temp.fname, Temp.lname, Temp.dob
FROM (
   SELECT m.fname, m.lname, m.dob, 
   COUNT(YEAR(dob)) AS CNT FROM TableName m 
   GROUP BY m.fname, m.lname, m.dob
) AS Temp ---- Sub query
WHERE Temp.CNT > 1 ---- Here check if there is any year greater than 1

Upvotes: 1

Mr.Alvaro
Mr.Alvaro

Reputation: 109

I hope this helps

SELECT fname, lname, dob
FROM students
WHERE dob IN (SELECT dob FROM students GROUP BY dob HAVING count(*) > 1);

Make sure to check your database config for the dob column, when running the query on my end to test if it works, I got a date error because there was no february 29 in 1981 :D

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270883

You can use exists:

select e.*
from exampledata ed
where exists (select 1
              from exampledata ed2
              where ed2.dob = ed.dob and ed2.id <> ed.id
             );

You can check if there are duplicates by simply doing:

select ed.dob, count(*), group_concat(ed.id)
from exampledata ed
group by ed.dob
having count(*) > 1;

Upvotes: 1

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7322

This works on Oracle, and I believe it will work on MySQL too:

select *
  from xtb
 where xfl in (select xfl 
                 from xtb
                group by xfl
               having count(*) > 1)
 order by xfl

Upvotes: 1

tyro
tyro

Reputation: 1428

Try the following

SELECT s1.* FROM `students`s1
INNER JOIN `students` s2 on  s1.`dob` = s2.`dob` and s1.`id`<> s2.`id`
GROUP BY s1.`id`

Upvotes: 1

Related Questions