skip to Main Content

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?

2

Answers


  1. 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'
    
    Login or Signup to reply.
  2. 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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search