• 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

    • LibreOffice narrows gap with Microsoft Office in 25.8 Beta 1 by David Uzondu The Document Foundation has released LibreOffice 25.8 Beta 1 for public testing on Linux, macOS, and Windows. This is the second pre-release for the 25.8 cycle and the foundation says that the final, stable version of LibreOffice 25.8 is expected to land at the end of August 2025. Starting off with Writer, LibreOffice's Word, the developers have finally addressed some long-standing annoyances, including a new command to easily insert a paragraph break right before a table. This beta also introduces a useful privacy feature in its Auto-Redact tool, letting you strip all images from a document with a single option. To use it, go to Tools and select the Auto-Redact option: The application has improved its ability to handle different languages for punctuation, preventing mix-ups in multilingual documents. Other notable improvements have also been made. A new hyphenation rule lets you choose to prevent a word from splitting at the end of a page, moving the whole line to the next page instead. Microsoft Word has had this feature for years now. The Navigator now displays a handy tooltip with word and character counts for headings and their sub-outlines. Scrolling behavior when selecting text has been improved, making it less erratic. A new command with a keyboard shortcut was added for converting fields into plain text. Calc gets a lot of new functions that bring it closer to its competitors like Excel, including TEXTSPLIT, VSTACK, and WRAPROWS. Impress now properly supports embedded fonts in PPTX files, which should reduce headaches when sharing presentations with PowerPoint users. Alongside these additions, the project is also cleaning house; support for Windows 7, 8, and 8.1 has been completely dropped. There are also smaller UI tweaks across the suite, like allowing a single click to enter rotation mode for objects in Writer and Calc. macOS users get better integration, with proper support for native full screen mode and new window management features from the Sequoia update. In terms of performance, the team has optimized everything from loading huge DOC files and XLSX spreadsheets with tons of conditional formatting to simply switching between sheets in Calc. These improvements should be noticeable, especially when working with complex documents. A new application-wide "Viewer mode" has also been implemented, which opens all files in a read-only state for quick, safe viewing. On a related note, The Document Foundation has joined efforts by the likes of KDE to encourage Windows 10 users to switch to Linux. Also, you might have heard that Denmark, in a bid to lessen its reliance on Microsoft, has decided to make a full switch to LibreOffice, with plans to begin phasing out Office 365 in certain ministries as early as next month. If you're interested in this release, you can read the full release notes and download the binaries for your platform: Windows, macOS (Intel | Apple Silicon), or Linux (DEB | RPM). You can also get the latest stable version from our software stories page.
    • Until it can be used 100% offline (ie: PST file support or equiv) not even considering it. I'll jump to Thunderbird first which has gotten a LOT better since the last time I looked at it.
  • Recent Achievements

    • Explorer
      Case_f went up a rank
      Explorer
    • Conversation Starter
      Jamie Smith earned a badge
      Conversation Starter
    • First Post
      NeoToad777 earned a badge
      First Post
    • Week One Done
      JoeV earned a badge
      Week One Done
    • One Month Later
      VAT Services in UAE earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      547
    2. 2
      ATLien_0
      230
    3. 3
      +FloatingFatMan
      158
    4. 4
      Michael Scrip
      114
    5. 5
      +Edouard
      111
  • Tell a friend

    Love Neowin? Tell a friend!