[SQL] Selecting common interests

[Guest]

I'm trying to create a SQL query that selects users based on their common interests...

I only want to select the users that have all 3 interests in common, not every user that has one or more of these interests... This is what i have come up with so far - just lots of subqueries...

  Quote

students.student_id in (select student_id from student_interests where student_id in (select student_id from student_interests where student_id in (select student_id from student_interests where interest_id = (select interest_id from interests where interest = 'xbox')) and interest_id = (select interest_id from interests where interest = 'programming')) and interest_id = (select interest_id from interests where interest = 'neowin'))

The student_interests table is just a pivot table containing the student_id and interest_id

and the interests table contains the interest_id and the interest string.

Is this the best way to do it or have is there a better way of doing it.

thanks guys...

[M_Lyons10]

Well, it looks alright to me (Not that I'm a SQL expert or anything). But you should use Parameters. I've been told a number of times that it's a better method and in the past I've found that sometimes it works when the above method does not (And it's easier to read too).

Like so:

students.student_id in (select student_id from student_interests where student_id in (select student_id from student_interests where student_id in (select student_id from student_interests where interest_id = (select interest_id from interests where interest = @P0)) and interest_id = (select interest_id from interests where interest = @P1)) and interest_id = (select interest_id from interests where interest = @P2))

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter = new OleDbDataAdapter(command);

command.Parameters.Add("@P0eDbType.VarChar).Value = "xbox";

command.Parameters.Add("@P1eDbType.VarChar).Value = "programming";

command.Parameters.Add("@P2eDbType.VarChar).Value = "neowin";

Good luck. :)

[Guest]

Thanks, I might look into that... these last few queries look awfully messy :p

[fo7557]

Try this:

SELECT DISTINCT student_interests.student_id
FROM student_interests si1,
  student_interests si2,
  student_interests si3,
  interests i1,
  interests i2,
  interests i3,
WHERE si1.interest_id = i1.interest_id
AND si2.interest_id   =i2.interest_id
AND si3.interst_id    =i3.interest_id
AND i1.interest       ='xbox'
AND i2.interest       ='programming'
AND i3.interest       ='neowin'
AND si2.student_id    = si1.student_id
AND si3.student_id    = si1.student_id