Not sure about complex query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Not sure about complex query

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Not sure about complex query

    Hi

    I am trying to use SQL to select rows which don't match certain criteria - let me explain!

    There are three tables - committees, committeememberships and users.

    committees holds details on committees - e.g. the name, a description
    committeememberships holds details on who is a member of what committee using ID numbers - each committee has a unique ID as does each user.
    users holds details on users.

    I have successfully managed to extract a list of committees that a user IS a member of - SELECT * from committeememberships INNER JOIN committeememberships.committeeid=committees.committeeid where committeememberships.userid=x;

    But I am confused as to how to extract a list of committees the user IS NOT a member of?

    I thought:

    SELECT * from committeememberships INNER JOIN committeememberships.committeeid=committees.committeeid where NOT committeememberships.userid=x;

    But that simply returns a list of all other users and committee memberships (all combinations other than the WHERE NOT criteria)

    Is what I'm asking possible?

    Thanks :-)

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    How about
    Code:
    SELECT * from committeememberships 
    INNER JOIN committeememberships.committeeid=committees.committeeid 
    where  committeememberships.userid <> x;
    I find it odd there is no ON part of your JOIN
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Ah yes, there actually is but I mistyped! Sorry!

    SELECT * from committeememberships INNER JOIN committees ON committeememberships.committeeid=committees.committeeid where committeememberships.userid=x;

    is what gives me a list of current committees that the user belongs to.

    I have tried what you suggested (slightly modified to include the ON argument so-

    SELECT * from committeememberships
    INNER JOIN committees ON committeememberships.committeeid=committees.committeeid
    where committeememberships.userid <> x;
    )

    but all that does is returns every other committee membership except for ones from the user ID entered.

    I am using ASP and I guess it could be done the web server end by something like:

    Select * from committees
    -For each x in committees
    --Select * from committeememberships where userid=x and committeeid=y
    ---If EOF then must not be a member
    ---Else must already be a member
    -Next x

    But that doesn't seem particularly efficient?

    Thank you for the welcome and any help :-)

Similar Threads

  1. Union query
    By peljo in forum Database
    Replies: 4
    Last Post: 12-17-2007, 12:38 PM
  2. SQL2000 remote SQL query
    By lightningtechie in forum Database
    Replies: 1
    Last Post: 02-07-2006, 09:34 AM
  3. Complex number
    By burung in forum Java
    Replies: 3
    Last Post: 08-01-2005, 06:59 AM
  4. Complex query
    By will nimmo in forum Database
    Replies: 2
    Last Post: 10-07-2002, 02:32 PM
  5. complex SQL query
    By mark in forum VB Classic
    Replies: 2
    Last Post: 03-24-2000, 03:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center