Couple of basic SQL query questions

Question

Hello,

Basically I got a small event system going on, but I'm having a couple of strange SQL query problems. 1st one I need to find all peoples names which have signed up for all 3 events. I tried to do:

SELECT name 
FROM users 
NATURAL JOIN events 
WHERE events.id = '4' AND events.id = '7' AND events.id = '8'

But it returns zero rows, even tho there are users that have signed up for all 3 events

2nd one, I need to find a people who signed up for event 4 but not for event 7 I tried:

SELECT name 
FROM users 
NATURAL JOIN events 
WHERE events.id = '4' AND events.id !='7'

It returns the same results as without the != mark, as it should at least be eliminating a few results.

Thanks in advance.

Answer 1

First of all, events.id cannot possibly be equals to 4, 7 and 8 at the same time (I think you are looking for the OR operator here). The second problem is that you can't compare the same column to 4 and 7 at the same time - it will look for things that are 4 and different than 7 (which 4 certainly is last time I checked).

Answer 2

As stated, events.id can only hold a single value; use OR if you want to find multiple values. Also, since it can only be a single value it will automatically be not equal to every other value. Did you perhaps mean to do a join with a second copy of events and check in the other copy?

Answer 3

You are trying to match multiple rows in your result set. To do this, you need to roll those rows up onto one row by joining the "events" table multiple times. For example:

SELECT name 
FROM users INNER JOIN events AS e4 
    INNER JOIN events AS e7 
    INNER JOIN events AS e8
WHERE e4.id = '4' 
  AND e7.id = '7' 
  AND e8.id = '8'

Answer 4

How about this for the 1st question.

Select name 
from users u1
where exists
(select name
 from users
 where users.name = u1.name
 and events.id = 4
)
and exists
(select name
 from users
 where users.name = u1.name
 and events.id = 7
)
and exists
(select name
 from users
 where users.name = u1.name
 and events.id = 8
)

2nd question

Select name 
from users u1
where exists
(select name
 from users
 where users.name = u1.name
 and events.id = 4
)
and not exists
(select name
 from users
 where users.name = u1.name
 and events.id = 7
)

Answer 5

Never used a natural join (It's not avaliable in SQL Server) and your structure seems a bit odd to me, but try:

SELECT name 
FROM users 
NATURAL JOIN events 
WHERE events.id = '4' OR events.id = '7' OR events.id = '8'  
GROUP BY name
HAVING count(*) =3

But are you really storing the different userids (You aren't using names for ids are you?) in the events table? You should have an event attendee table related to the event table and the event table shoudl only describe the specidfic event not the mulitple of people attending it.

Answer 6

Instead of trying to join through using NATURAL JOIN, constrain the join.

Is it multiple events per user, so that the event.userId is a foreign key to event.id? If so, try

Select name
from users u1
where users.id in
   (Select userId 
    from events
    where events.id=4) and
 users.id in
   (Select userId 
    from events
    where events.id=7) and
 users.id in
   (Select userId 
    from events
    where events.id=8);

And

Select name
from users u1
where users.id in
   (Select userId 
    from events
    where events.id=4) and
 users.id not in
   (Select userId 
    from events
    where events.id=7);