CODEWITHSUNDEEP
mysqlsql
David Dennis
David Dennis

Reputation: 722

Checking if two People share the same phone number in SQL

So I have a table which contains StudentIDs and Phone Numbers.. Some students share the same Phone Number, and I want to select and display those students.

For example.

+-----------+-----------+--------------+
| StudentID | StudentID | PNumber      |
+-----------+-----------+--------------+
|      1000 |      1000 | 970-555-1000 |
|      1000 |      1000 | 970-555-1010 |
|      1100 |      1100 | 970-555-1100 |
|      1200 |      1200 | 303-555-1200 |
|      1200 |      1300 | 303-555-1200 |
|      1300 |      1200 | 303-555-1200 |
|      1300 |      1300 | 303-555-1200 |
|      1300 |      1300 | 303-555-1210 |
|      1400 |      1400 | 970-555-1400 |
|      1500 |      1500 | None         |
|      1600 |      1600 | 970-555-1600 |
|      1600 |      1700 | 970-555-1600 |
|      1700 |      1600 | 970-555-1600 |
|      1700 |      1700 | 970-555-1600 |
|      1800 |      1800 | 970-555-1800 |
|      1900 |      1900 | 970-555-1900 |
|      2000 |      2000 | 970-555-2000 |
|      2000 |      2000 | 970-555-2010 |
|      2001 |      2001 | 970-555-2001 |
+-----------+-----------+--------------+

This should output:

1200 | 1300 | 303-555-1200
1300 | 1200 | 303-555-1200
1600 | 1700 | 970-555-1600
1700 | 1600 | 970-555-1600

Upvotes: 2

Views: 1235

Answers (2)

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

try join itself:

select t1.StudentID,t2.StudentID,t1.PNumber
from Student t1
join Student t2 on t1.PNumber= t2.PNumber
where t1.StudentID <> t2.StudentID

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

Use a self JOIN:

SELECT
    s1.StudentID, s2.StudentID, s1.PNumber
FROM
    students s1 INNTER JOIN students s2 USING(StudentID)
WHERE s1.PNumber = p2.PNumber;

Upvotes: 1

Related Questions