Join TSR now and chat about whatever you’re intoSign up now
    • Thread Starter
    Offline

    0
    ReputationRep:
    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?
    Offline

    17
    ReputationRep:
    (Original post by rawsex)
    Any help?

    What are you trying to achieve as your outcome? Your current query would surely just select everything from Events where Events.id is not equal to any EventPareticipants.id, no?

    If I'm reading you right, you're wanting to report on every event that a person is not competing in, yes?

    If so, you could do:

    Code:
    SELECT DISTINCT(name)
    FROM Events AS t01
    INNER JOIN EventParticipants AS t02
     ON t01.id = t02.eventID
    WHERE userID <> '{UserID}'
    Offline

    0
    ReputationRep:
    Mikey is right!

    By the way... Next time if you get stuck trying to create a query; use a query builder!

    Like MySQL Workbench!
 
 
 
Poll
If you won £30,000, which of these would you spend it on?
Useful resources

The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Quick reply
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.