CODEWITHSUNDEEP
phpmysqlsql
user3430542
user3430542

Reputation: 5

Select all upcoming events where a user participate in

I have problem with sql that is suppose to select all upcoming events where a specific user participate in. This is sample how my tables looks like

events

  id  |      timestamp      |  name  | deleted
 200  | 2014-04-01 14:00:00 | test 1 |    0
 201  | 2013-06-30 20:50:00 | test 2 |    0
 205  | 2014-04-02 09:00:00 | test 3 |    1
 210  | 2014-04-03 08:55:34 | test 4 |    0

responses

  id  | user | event |      timestamp      | canceled
 1001 |  50  |  201  | 2013-06-01 10:15:39 |    0
 1002 |  23  |  205  | 2014-02-15 09:32:00 |    0
 1003 |  50  |  210  | 2014-02-15 10:00:00 |    0
 1004 |  50  |  210  | 2014-02-16 20:00:00 |    1
 1005 |  50  |  200  | 2014-02-16 20:05:40 |    0

To explain the structure: If an admin deletes an event the deleted value of the event is set to 1. If an user registers to an event, a new row in responses is added with the user id, the event id, the current time and canceled = 0. He is also able to cancel his registration. In this case a new row is added to responses with canceled = 1. So on each change of the registration status of an user to an event a new row is added. It's possible that a users registered for an event, than cancel his registration and than register a second time for the same event.

Now I want to select all upcoming events where the user with the id 50 is registered (and has not canceled his registration). This should be the result:

event.id |   event.timestamp   | responses.id
   200   | 2014-04-01 14:00:00 |     1005

At the moment i'm using to SELECT statements and php to get the result shown above. EDIT this is what I do at the moment: first I do

"SELECT id,timestamp FROM events WHERE timestamp>NOW() AND deleted=0"

than I run a php foreach loop to perform this statement for each upcoming event

"SELECT id,canceled FROM responses WHERE user = 50 AND event=$event->id ORDER BY timestamp DESC LIMIT 1"

If a row exists I check with php if canceled is 0.

How to solve the problem with a single SELECT statement?

Upvotes: 0

Views: 184

Answers (5)

Strawberry
Strawberry

Reputation: 33945

SELECT e.* 
  FROM events e 
  JOIN responses r
    ON r.event_id = e.id
  JOIN
     ( SELECT user
            , event_id
            , MAX(timestamp) max_timestamp
         FROM responses 
        GROUP
           BY user
            , event_id
     ) x
    ON x.user = r.user
   AND x.event_id = r.event_id
   AND x.max_timestamp = r.timestamp
 WHERE e.timestamp > NOW() 
   AND r.user = 50 
   AND r.canceled = 0 
   AND e.deleted = 0;

Upvotes: 0

akr
akr

Reputation: 739

select * from (SELECT e.id,e.name,r.userid,r.cancelled,MAX(cancelled) as flag
from event e 
join responses r
on r.event_id=e.id where
e.deleted=0  group by userid  order by e.id) as users where flag=0;

Upvotes: 0

NiJo
NiJo

Reputation: 26

You can subselect the MAX(canceled) in order to see if the user canceled the registration. Something similar to:

SELECT resp.user, events.id, events.name, events.timestamp AS event_time
FROM events
LEFT JOIN (SELECT user, event, MAX(canceled) canceled FROM responses GROUP BY user, event) resp
ON (events.id=resp.event)
WHERE events.timestamp>NOW() AND events.deleted=0 AND resp.canceled=0

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

The eaiset way is to change your approach of adding a separate "cancelled event" record to the response table. This basically makes no sense. In order for a user to cancel an event, I am assuming you have to know the specific response id to be cancelled. You should update the cancelled flag to 1 in such a case rather than adding a new row to the table. That makes your query as simple as:

SELECT
    e.id,
    e.`timestamp`,
    r.id
FROM
    events AS e
    INNER JOIN responses AS r
        ON e.id = r.event
WHERE
    r.user = ?
    AND r.canceled = 0
    AND e.deleted = 0

Upvotes: 0

Rottingham
Rottingham

Reputation: 2604

This may do the trick

You will select the responses, filtered by the USER name, and join the event that those responses belong to.

SELECT * FROM `responses`
JOIN `events` ON (
    `events`.`id` = `responses`.`event` AND
    `events`.`deleted` = 0
)
WHERE `responses`.`user` = :USER AND
`responses`.`canceled` = 0

The reason for selecting the responses is because it holds the discriminating column, the user.

Upvotes: 0

Related Questions