MySQL - Performing an IF statement for each row in a query

Question

I have a hurdle I'm trying to overcome with a website I'm building. I know what I want to achieve but I'm still pretty new to mysql so I'm not exactly sure how it's supposed to fit together.

Setup:

I have 4 tables:

• guestList
• eventList
• inviteList
• rsvpList

The descriptions of the tables:

guestList

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| _id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| groupName | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

eventList

+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| _id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| description         | varchar(50) | YES  |     | NULL    |                |
| eventDate           | date        | YES  |     | NULL    |                |
| eventTime           | time        | YES  |     | NULL    |                |
| address             | varchar(50) | YES  |     | NULL    |                |
| locationDescription | varchar(50) | YES  |     | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+

inviteList

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| _id     | int(11) | NO   | PRI | NULL    | auto_increment |
| guestId | int(11) | YES  |     | NULL    |                |
| eventId | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

rsvpList

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| _id     | int(11) | NO   | PRI | NULL    | auto_increment |
| guestId | int(11) | YES  |     | NULL    |                |
| eventId | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

Goal:

I want to be able to pull a list of the events that guests are invited to AND a boolean value on whether or not they have rsvp-ed to said events.

I already have the statement for pulling only events that they've been invited to:

SELECT e.description,e.eventDate,e.eventTime,e.locationDescription,e.address
FROM eventList AS e 
JOIN inviteList AS i ON e._id = i.eventId 
WHERE i.guestId = ?;
-- where the guestId is passed in by the php script executing the query

And I think adding in the following if statement will work:

IF (r.guestId = x AND r.eventId = y, 1, '')

But what I'm stuck on is how exactly to fit this in so that the if statement evaluates for each row returned from the SELECT statement grabbing the events.

I've tried the query as:

SELECT e.description,e.eventDate,e.eventTime,e.locationDescription,e.address, IF(r.eventId = e._id AND r.guestId = 1, 1, 0)
FROM eventList AS e 
JOIN inviteList AS i ON e._id = i.eventId 
JOIN rsvpList AS r ON e._id = r.eventId
WHERE i.guestId = 1;

But that returns only the events that the guest has rsvp-ed to, not all of them and then a boolean flag for each event.

I searched around for stuff like 'using the result from one query for another' but the answers that I was finding didn't really fit my situation (I fully acknowledge that it may be due to my green-ness with mysql)

Anyone have a solution and explanation? I want it to work but I def also want to understand how it's working.

Thanks!

Answer 1

To get desired results, use LEFT JOIN rsvpList instead of [INNER] JOIN. Thus, in resultset r.eventId will be NULL for rows in eventList table that don't have corresponding rows in rsvpList.

 SELECT e.description,e.eventDate,e.eventTime,e.locationDescription,e.address, 
 IF(r.eventId = e._id AND r.guestId = 1, 1, 0)
 FROM eventList AS e 
 JOIN inviteList AS i ON e._id = i.eventId 
 LEFT JOIN rsvpList AS r ON e._id = r.eventId
 WHERE i.guestId = 1;