Please help me design a sql query for this problem

For a particular name i want to fetch other names who have lived in three or more cities l开发者_JS百科ived by this person.

---

this is what you should do:

redesign your database to have a city table:

city(id int, name varchar)

and a user table:

user(id int, name varchar, ...)

and a user_city table:

user_city(user_id int, city_id int)

that alone will eliminate the limit of 10 cities per user.

to find the cities lived in by a user:

select city_id form user_city where user_id = ?

now how you would find users that lives in 3 or more cities from that list? one way to do it would be to count the number of cities from the list each user lived in, something like:

select user_id,count(*) n 
from user_city 
where city_id in (select city_id 
                  from user_city 
                  where user_id = ?) 
group by user_id having n >= 3;

I didn't really test this, but it should work.

you will also have to figure out how to index those tables.

---

You'd need binomial(10,3)^2 OR conditions to do your query. Thats 14 400. You do not want to do that.

---

You need to redesign your table instaed of

name , city1 , city2 , city3 ,city4 , city5 ,city6 , city7 , city8 , city9 city10

it should be more like

Person, City, rank
------------------
name , city1 ,1
name , city2 ,2 
name , city3 ,3 
name , city4 ,4 
name , city5 ,5 
name , city6 ,6 
name , city7 ,7 
name , city8 ,8 
name , city9 ,9 
name , city10,10

and take TomTom's advice and learn about data normalization!

---

Respecting your request to not redesign the database

My untried idea, no way to test it right now

Make a view (name, city) by unioning select name, c1, select name, c2 etc...

Then:

select m2.name from myview m1
inner join myview m2 on m1.city = m2.city
where m1.name = @Name AND m2.Name!=@Name
group by m2.name
having count(m2.name) > 2 

---

Yeah.

You send the table back to whoever designed it with a comment to learn hwo to design tables. First normal form, normalization.

Once the table follows SQL rules, the query is pretty easy.

---

Try something like this:

SELECT PersonName,COUNT(*) AS CountOf
    FROM (SELECT PersonName,city1 FROM PersonCities WHERE city1 IS NOT NULL
          UNION SELECT PersonName,city2 FROM PersonCities WHERE city2 IS NOT NULL
          UNION SELECT PersonName,city3 FROM PersonCities WHERE city3 IS NOT NULL
          UNION SELECT PersonName,city4 FROM PersonCities WHERE city4 IS NOT NULL
          UNION SELECT PersonName,city5 FROM PersonCities WHERE city5 IS NOT NULL
          ...
         ) dt
    WHERE dt.city1 IN (SELECT city1 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city1 IS NOT NULL
                       UNION SELECT city2 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city2 IS NOT NULL
                       UNION SELECT city3 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city3 IS NOT NULL
                       UNION SELECT city4 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city4 IS NOT NULL
                       UNION SELECT city5 FROM PersonCities WHERE PersonName=..SearchPerson.. AND city5 IS NOT NULL
                       ...
                       )
        AND PersonName!=@SearchPerson
    GROUP BY PersonName
    HAVING COUNT(*)>=3

I don't have mysql, so here it is running using SQL Server:

DECLARE @PersonCities table(PersonName varchar(10), city1 varchar(10), city2 varchar(10), city3 varchar(10), city4 varchar(10), city5 varchar(10))
INSERT INTO @PersonCities VALUES ('Joe','AAA','BBB','CCC', NULL, NULL)
INSERT INTO @PersonCities VALUES ('Pat','BBB','DDD','EEE','FFF','GGG')
INSERT INTO @PersonCities VALUES ('Sam','FFF','BBB', NULL, NULL, NULL)
INSERT INTO @PersonCities VALUES ('Ron','HHH','DDD','EEE','FFF', NULL)
INSERT INTO @PersonCities VALUES ('Don','FFF','ZZZ','QQQ', NULL, NULL)

DECLARE @SearchPerson varchar(10)
SET @SearchPerson='Pat'

SELECT PersonName,COUNT(*) AS CountOf
    FROM (SELECT PersonName,city1 FROM @PersonCities WHERE city1 IS NOT NULL
          UNION SELECT PersonName,city2 FROM @PersonCities WHERE city2 IS NOT NULL
          UNION SELECT PersonName,city3 FROM @PersonCities WHERE city3 IS NOT NULL
          UNION SELECT PersonName,city4 FROM @PersonCities WHERE city4 IS NOT NULL
          UNION SELECT PersonName,city5 FROM @PersonCities WHERE city5 IS NOT NULL
         ) dt
    WHERE dt.city1 IN (SELECT city1 FROM @PersonCities WHERE PersonName=@SearchPerson AND city1 IS NOT NULL
                       UNION SELECT city2 FROM @PersonCities WHERE PersonName=@SearchPerson AND city2 IS NOT NULL
                       UNION SELECT city3 FROM @PersonCities WHERE PersonName=@SearchPerson AND city3 IS NOT NULL
                       UNION SELECT city4 FROM @PersonCities WHERE PersonName=@SearchPerson AND city4 IS NOT NULL
                       UNION SELECT city5 FROM @PersonCities WHERE PersonName=@SearchPerson AND city5 IS NOT NULL
                       )
        AND PersonName!=@SearchPerson
    GROUP BY PersonName
    HAVING COUNT(*)>=3

OUTPUT:

PersonName 
---------- -----------
Ron        3

(1 row(s) affected)

---

You need to normalize your database.

Doing that you will get the columns

Name, City (optionally CityOrder).

After that you will need to find a way to combine these results into what you need. Doing this you'll need to understand Join, Count and Group by.

---

Try this:

< table > Person
< fields > PersonId, PersonName |

< table > City
< fields > CityId, CityName |

< table > LivedIn
< fields > LivedInId, PersonId, CityId

Logically you would do the following things for each scenario:

1. Find the person who has lived in the maximum number of different cities:
   Make a list of the PersonId's (all people)
   Iterate over that and count the number of cities each person lived in
   Find the maximum cities lived in by anyone person
   Find the person name related to the personId that had the max cities
   

2. Find all people that lived in 3 or more cities as a give person
   Let's call the person Bob
   Make a list of all cities (the CityIds) that Bob lived in.
   Make a list which includes personId, and common cities (maybe a HashMap in Java)
   Iterate over the LivedIn table and update the count of how many cities are common
   Find all the people who have a count greater than 3
   

I would do this with a combination of Java and SQL but I am not that good with either so can't give you the code here without having to look a lot of stuff up.

---

Breaking this data out into three tables to provide a more flexible many-to-many relationship.

person table to store names
city table to store cities
person_city to relate the two (many to many)

To retrieve other people who have lived in 3 or more cities that navin has:

SELECT name FROM (
    SELECT
        p.name, COUNT(DISTINCT(city_id)) AS lived
    FROM person p 
    JOIN person_city pc ON (pc.person_id = p.person_id) 
    JOIN city c ON (c.city_id = pc.city_id) 
    WHERE city_id IN (
        SELECT c2.city_id 
        FROM city c2 
        JOIN person_city pc2 ON (c2.city_id = pc2.city_id) 
        JOIN person p2 ON (p2.person_id = pc2.person_id) 
        WHERE p2.name = 'navin' 
    )
    GROUP BY person_id HAVING lived >= 3
) AS multihome 
WHERE name <> 'navin';