mysql querying contacts at same address

ferhatyurdakulxd 0
Viewed 54

I have a table persons;

INSERT INTO `persons` (`tc`, `name`, `surname`, `gender`, `city`, `stret`, `apertment_number`) VALUES

What everyone is looking for is the name by which they are questioned, which is the same person to question what happened at the same address.

sample;

tc | name    | surname  | gender | city     | stret           | apertment_number
1  | Ahmet   | Caner    | M      | Istanbul | Taksim Caddesi  | 2
2  | Mehmet  | Duran    | M      | Burdur   | Adem Mahallesi  | 5
3  | Ayfer   | Gelmez   | F      | Istanbul | Taksim Caddesi  | 2
4  | Durmus  | Umman    | M      | Denizli  | Deniz Sokak     | 6
5  | Selman  | Cakir    | M      | Istanbul | Taksim Caddesi  | 2

When I query someone with name value, I want to find people who live at the same address.

Çıktı:

tc | name    | surname  | gender | city     | strett          | apertment_number
1  | Ahmet   | Caner    | M      | Istanbul | Taksim Caddesi  | 2
3  | Ayfer   | Gelmez   | F      | Istanbul | Taksim Caddesi  | 2
4  | Selman  | Cakir    | M      | Istanbul | Taksim Caddesi  | 2

I want a mysql query like this can you help me?

mysql sql
2 Answers

If same address means they have same city and street,then you can use follow

SELECT p1.* FROM persons p1
 JOIN persons p2 ON p1.city=p2.city AND p1.strett=p2.strett 
WHERE p2.name='Ahmet' AND p1.name!='Ahmet'
lucumt0

Count the rows per city and street. Show the rows where that count is greagter than one.

select tc, name, surname, gender, city, stret
from
(
  select p.*, count(*) over (partition by city, stret) as cnt
  from persons p
) address_counted
where cnt > 0
order by city, stret, name;
Thorsten Kettner0
Related