SQL Server Performance

Urgent: Help me to find this soln.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by haneesh, Feb 21, 2008.

  1. haneesh New Member

    Suppose a table contains fields such as member_id,item1,item2 and item3.Then how can we fetch common items between two rows.
    memid
    1
    2
    3
    item1
    a
    b
    d
    item2
    b
    c
    f
    item3
    c
    d
    g

    common items between first 2 rows is 'b' and 'c'. How can we write a query for this. thanks in advance
  2. patel_mayur New Member

    What exactly you are trying to do ?
    On what basis you will pick two rows for comparision ?
    In your example there is another common item "d" between record 2 & 3.
  3. haneesh New Member

    I'll explain with another eg. If i have a 2 tables first with member_id,mem_name and location and another with member_id, interest1,interest2 and interest3 as fields. Suppose the tables contains 1000 rechords. If a new member came , i want to compare that member's interests with all the members in the table and return the number of common interests in each rechord.
  4. patel_mayur New Member

    It would be easier if you dont allow user to enter free text for each of your three columns ( Interest1, 2 & 3 ). It would be difficult or rather inappropriate to compare text values span over three different fields.
    Make three groups of possile interests that user may have and use checkboxes like most of the internet site doing.
  5. Adriaan New Member

    Using a set number of columns is making your life more difficult than it needs to be. Create a "subtable", where you can add as many values as required per member. You can then do a self-join on this subtable to find matching interests:

    Member (MemberID, MemberName)
    Interest (InterestID, InterestDescription)
    MemberInterest (MemberID, InterestID)

    SELECT m1.MemberName, m2.MemberName, i.InterestDescription
    FROM Member m1 INNER JOIN MemberInterest mi1 ON m1.MemberID = mi1.MemberID
    INNER JOIN MemberInterest mi2 ON mi1.InterestID = mi2.InterestID AND mi1.MemberID <> mi2.MemberID
    INNER JOIN Interest i ON mi1.InterestID = i.InterestID
    WHERE i.InterestDescription = 'my interest blabla'

Share This Page