• 0

[SQL] Selecting common interests


Question

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...

Link to comment
https://www.neowin.net/forum/topic/864804-sql-selecting-common-interests/
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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. :)

  • 0

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 

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Elon Musk once again claims Tesla robotaxis are coming soon by David Uzondu Image via Depositphotos.com Tesla's CEO, Elon Musk, has announced a tentative start date of June 22 for the company's long-awaited public robotaxi service. According to a post on his social media platform X, the initial launch will be in Austin, Texas. Musk added a significant condition, however, saying "We are being super paranoid about safety, so the date could shift." The service is expected to begin with just 10 to 20 Model Y SUVs operating within a limited area and with remote human supervision. He also mentioned a plan starting June 28 for new Teslas to drive themselves from the factory to a customer's home. This is a moment many are probably waiting for, though it comes with a heavy dose of skepticism. Musk has made grand promises about self-driving before. This robotaxi network brings to mind the bold claims from all the way back in 2019 when the company said a similar service would launch the following year. That evidently did not happen. Federal regulators also have their doubts. Last year, the National Highway Traffic Safety Administration criticized Tesla for making its "Full Self-Driving" feature sound more capable than it actually is, demanding the company align its marketing with reality. Tesla is also driving into a field that is no longer empty. Waymo, Google's sibling company, is already a major player, offering hundreds of thousands of paid rides per week across Phoenix, San Francisco, Los Angeles, and even Austin. The company is so far ahead that it has begun testing in Tokyo. But being ahead means Waymo is also the first to face certain dangers. For example, on the evening of June 8, a group of protesters in downtown Los Angeles summoned Waymo vehicles during a demonstration. When the vehicles arrived, they slashed the tires, smashed the windows, and spray-painted the cars before setting three of them on fire. Which raises a thorny question for Tesla: if you can summon a car with no one inside, can you summon it just to destroy it? It's one thing for protesters to stumble upon a robotaxi and vandalize it; it's another for someone to use the app to call a driverless car to a secluded spot for a planned attack. With public sentiment around Musk so divided, especially given his DOGE shenanigans and his recent face off with Donald Trump, that's not just a theoretical problem. We've already seen this hostility play out in attacks where people vandalize Teslas, carving swastikas into them and spray painting slogans like "Burn More Teslas" on walls.
    • This is actually quite useful. But why wouldn't they implement this in the local file system? The code is obviously all there now... maybe in 5 years.
    • The new "Story Cards" in the Software section are nice, but I wish they had the product icon included. I use this section to identify updates for software that I use regularly, and it's sometime difficult to identify the software without the product icon. Thanks for your consideration. pelaird
    • Mozilla really needed to focus on their core product for a while now. I will not mourn the death of pocket or AI garbage. One thing they don't do that I believe they should is advertise more, and not just to their core audience, especially their additional services. Let people know they actually exist.
    • Is this like tailscale?
  • Recent Achievements

    • Week One Done
      Food-Beverages-Nutrition earned a badge
      Week One Done
    • Week One Done
      Tech Dogs earned a badge
      Week One Done
    • Enthusiast
      computerdave91111 went up a rank
      Enthusiast
    • Week One Done
      Falisha Manpower earned a badge
      Week One Done
    • One Month Later
      elsa777 earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      524
    2. 2
      ATLien_0
      271
    3. 3
      +Edouard
      199
    4. 4
      +FloatingFatMan
      196
    5. 5
      snowy owl
      138
  • Tell a friend

    Love Neowin? Tell a friend!