i have a table as given above with name and 10 cities in which he lived during his lifetime. name , c1 , c2 ... ,c10. For a particular name i want to fetch other people who have lived in three or more cities lived by this person.
The best/easiest way to achieve what you want would not be through a complex SQL statement, but by scripting. Generally, you would run a statement to pull the row of data you want (SELECT * FROM [yourtable] WHERE name='[name]') load the 10 cities returned into an array (goodcity[]), pull all the other rows of data (SELECT * FROM [yourtable]), loop through every one of them comparing them to each and every goodcity[] element and counting each match for every row. Then if the count of matches for that record is 3 or more save that records name. If you need more help, tell me what scripting language you will be using and I can write more specific code.
This table is not scalable! How about if we've user like: neeraj,'kolkata','kolkata','kolkata','kolkata','kolkata','kolkata','kolkata','kolkata','kolkata','kolkata' or neeraj,'kolkata','delhi','kolkata','delhi','kolkata','delhi','kolkata','delhi','kolkata','delhi' Regards
Drop me a PM and we can do this through either IM or Email... this forum seems to have people suggesting flaws in your system, now the solution you're after.