Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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;
share|improve this answer
    
Awesome! Worked like a charm. Thanks!!! –  Chris Schmitz Feb 8 '13 at 19:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.