close ad
WARNING PC USERS: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Help with SQL Query

Thread began 10/25/2015 10:51 am by Daryl | Last modified 10/26/2015 2:49 pm by Ray Borduin | 546 views | 5 replies |

DarylBeta Tester

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.

Sign in to reply to this post

CraigRBeta Tester

to show rows from the 'main' table, where there may or may not be a matching record in the linked table, try a left join rather than an inner join

http://www.w3schools.com/sql/sql_join_left.asp

Sign in to reply to this post

DarylBeta Tester

Hi Craig

Ive tried both a LEFT JOIN and also an OUTER LEFT JOIN all with no luck

Sign in to reply to this post

CraigRBeta Tester

See PM

Sign in to reply to this post

DarylBeta Tester

PM

Sign in to reply to this post

CraigRBeta Tester

PM

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...