help with mysql join logic

beckstei

I have two tables: Events and People. They are used for registration. People contains name and userID. Event contains userID and eventID. I need to compile lists of people who are NOT registered for any given event.

Example data:

People contains the following

Name UserID
John jsmith
Mary mjones
Fred fflint
Wilma wflint

Events contains the following:

EventID UserID
101 fflint
101 jsmith
102 wflint
102 mjones
102 fflint
103 mjones
103 jsmith

I want to contact all who have not registered for event 101, so I need to get People.name where Events.EventID!=101. So the answer that I want is wflint and mjones.

Using mysql version 3.23 -- A subquery would work, but it's not supported.

Any help would be appreciated.

Thanks
B

Erkilite

give this a shot

select t1.userID as userID, t1.name as name from People as t1, Event as t2 where t2.eventID!=101 and t2.userID = t1.userID;

beckstei

I appreciate the response, but your query would result in the following list:

Wilma wflint
Mary mjones
Fred fflint
Mary mjones
John jsmith

Even if I throw in a 'distinct username' I get Wilma, Mary, Fred and John because they have all registered for something other than event 101 and I still don't know who hasn't registered for event 101.

What I really need is
select name from people where people.userID NOT IN (select userID from Events where eventID=101).

But I don't know how to do this in the version of mysql that I'm using.

Thanks
B

barand

Try with temporary table :-

CREATE TABLE tempevent (eventID varchar(10), userID varchar(10))

INSERT INTO tempevent SELECT eventID, userID FROM events WHERE eventID = '101'

SELECT DISTINCT p.name FROM people p LEFT JOIN tempevent e
ON p.userID = e.userID
WHERE e.eventID is NULL

DROP TABLE tempevent

Erkilite

seems like a lot of work, why not just do a nested select then?

select from (select from....)....

beckstei

whenever I try to use subselects (nested selects) i get a syntax error message. I assumed this was because subselects aren't supported, but perhaps I'm just getting it wrong.

Are subselects supported in mysql 3.23?

Thanks
B

barand

Originally posted by Erkilite
seems like a lot of work, why not just do a nested select then?

select from (select from....)....

And what should those nested selects be?

barand

Originally posted by beckstei
Are subselects supported in mysql 3.23?

Thanks
B

No, not till 4.1.

I'm on 3.23, that's why I use the temp table.

ultraslacker

Originally posted by beckstei

I want to contact all who have not registered for event 101, so I need to get People.name where Events.EventID!=101.

SELECT p.name
FROM People p
LEFT OUTER JOIN Events e
ON (e.EventID = 101 AND p.UserID = e.UserID)
WHERE e.UserID IS NULL

Bastern

I gave it a shot and managed to pick out people who had only signed up to evnet 101, i.e. Fred Flint and John Smith, using:

SELECT people.name
FROM people, event WHERE event.userID = people.userID
AND event.eventID = 101;

Regards, Bastern 🙂