Help with SQL Query
Hi everyone, I was hoping that someone might be able to hep me with an SQL Query to accomplish a task..
I currently have a table which is similar to the results page created by Data Assist. The table shows the date and title of an event with a link to a detail page.
the SQL Query I am using is...
SELECT *
FROM activities
INNER JOIN activityVenues ON activities.activityVenue = activityVenues.venueID
INNER JOIN activitySections ON activities.activitySection = activitySections.activitySectionID
WHERE activities.activityEnd >= CURDATE() AND activitySections.activitySectionID = 3
ORDER BY activities.activityStart ASC
When you go to the details page, you can confirm your attendance by using an insert form which adds a record into a linking table containing your ID, the Event ID and your status (1 for attending, 2 for not attending).
I have been asked to add an icon to the original page showing a status icon (attending, not attending, unknown) however every query I have tried will show a list of events that have had an entry in the linking table, but wont show an event if you haven't signed up...
SELECT *,
signupActivity.signupActivityStatus,
signupActivity.signupActivitychildID
FROM activities INNER JOIN activityVenues ON activities.activityVenue = activityVenues.venueID
INNER JOIN activitySections ON activities.activitySection = activitySections.activitySectionID
INNER JOIN signupActivity ON signupActivity.SignupActivityID = activities.activityID
WHERE activities.activityEnd >= CURDATE() AND activitySections.activitySectionID = 3 AND signupActivity.signupActivitychildID = 4
ORDER BY activities.activityStart ASC
Ive also tried
SELECT *,
signupActivity.signupActivityStatus,
signupActivity.signupActivitychildID
FROM activities INNER JOIN activityVenues ON activities.activityVenue = activityVenues.venueID
INNER JOIN activitySections ON activities.activitySection = activitySections.activitySectionID
INNER JOIN signupActivity ON signupActivity.SignupActivityID = activities.activityID
WHERE activities.activityEnd >= CURDATE() AND activitySections.activitySectionID = 3 AND signupActivitychildID is null or signupActivitychildID = 4
ORDER BY activities.activityStart ASC
Does anyone have any suggestions on how I can get my query to work which will show a complete list of events even if an entry doesn't exist on the linking table? Im guessing it has something to do with the JOIN that Im using.
