#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0

    MySQL query doesn't seem to quite work


    So, I want to execute a relatively complex SQL statement, bearing in mind I have a rather amateur grasp of SQL.

    I am using the MySQL flavour of SQL and was wondering if you could help.

    Basically, I have two tables - one is called "events" and another is called "event participants"

    They each (simplified) look something like:

    ---Events---

    id
    name

    ------------

    ---EventParticipants---

    id
    eventId
    userId

    ------------------------

    EventParticipants.eventId corresponds with the id field in 'Events'


    What I want to do is SELECT all of the events from 'Events' where the id <> event id where userId=some number


    'SELECT * FROM Events WHERE id <> (SELECT id FROM EventParticipants WHERE userId <> {some user ID})' is essentially my query.

    However, the subquery 'SELECT EventParticipants WHERE userId <> {some user ID}' can sometimes return multiple values as a single userId can be mapped to several rows in EventParticipants. And I don't think you can compare, in the main body of the query 'WHERE id <> {multiple values'}

    Any help?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,259
    Rep Power
    4279
    Originally Posted by jamesaps
    What I want to do is SELECT all of the events from 'Events' where the id <> event id where userId=some number
    could you please rephrase this in english
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0
    Originally Posted by r937
    could you please rephrase this in english
    SELECT all events from from an Events Table, where the ID field in this table is not equal to a range of IDs in another table that correspond to a range of userId values.

    So I have

    Table(fields)
    ---
    Events (id, name)
    EventParticipants(id, userid, eventid)
    ---

    Let's say I have userid = 1. And in EventsParticipants there are 6 rows where this is the case. All of these rows will correspond to an eventid number. What I want to do is select all of the rows in the Events table where the id field does not equal any of the eventids in the EventParticipants table with userid = 1.

    Is this any clearer?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,259
    Rep Power
    4279
    Originally Posted by jamesaps
    Is this any clearer?
    very much so

    you are describing a scenario known as unmatched

    it can be written with NOT IN subquery

    i prefer LEFT OUTER JOIN with IS NULL

    here's my sql template --
    Code:
    SELECT
      FROM
    LEFT OUTER
      JOIN
        ON
       AND
     WHERE              IS NULL
    and here it is using your specifics --
    Code:
    SELECT e.id
         , e.name
      FROM events AS e
    LEFT OUTER
      JOIN eventparticipants AS ep
        ON ep.eventid = e.id
       AND ep.userid = 1
     WHERE ep.eventid IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0
    Originally Posted by r937
    very much so

    you are describing a scenario known as unmatched

    it can be written with NOT IN subquery

    i prefer LEFT OUTER JOIN with IS NULL

    here's my sql template --
    Code:
    SELECT
      FROM
    LEFT OUTER
      JOIN
        ON
       AND
     WHERE              IS NULL
    and here it is using your specifics --
    Code:
    SELECT e.id
         , e.name
      FROM events AS e
    LEFT OUTER
      JOIN eventparticipants AS ep
        ON ep.eventid = e.id
       AND ep.userid = 1
     WHERE ep.eventid IS NULL
    Thanks so much!!!

    What does the 'OUTER' bit do out of curiosity?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,259
    Rep Power
    4279
    Originally Posted by jamesaps
    What does the 'OUTER' bit do out of curiosity?
    it's an optional keyword

    i always write it, as a "best practice" reminder that it's an outer join and what that implies for unmatched rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo