Darylt Posted November 7, 2015 Share Posted November 7, 2015 mySQL Version - 5.5.46 SELECT * FROM (activities left join signupActivity on activityID = SignupActivityID) left join children on signupActivitychildID = childrenID Where activitySection = 3 AND childrenID = 33 No Errors are displayed however the output is not what I am trying to achieve.I have three tables as follows Children Table - childrenID - childrenFirstName - childrenLastName Activities Table - activityID - activityName - activityStart - activitySection Signup Activity Detail - signupID - signupActivityID - signupChildrenID - signupStatus I child can signup to an activity which creates a row in the SIgnup table which adds the Child and Activity ID along with Signup Status as a numerical value. I would like an output similar to:Activity Name - Activity Start Date - Activity Venue - Activity Status Climbing - 1st Jan 2015 - Climbing Wall - 1 Canoeing - 3rd May 2015 - Lake - 2 Horse Riding - 7th June 2015 - Paddock - NULL However I am only getting the first two entries as the third entry isnt showing due to the NULL Quote Link to comment Share on other sites More sharing options...
Barand Posted November 7, 2015 Share Posted November 7, 2015 Because you are LEFT JOINing to signup and children tables you cannot put selection conditions on those tables in a WHERE clause. If you do then the LEFT JOINS will behave like INNER JOINS. You need to put those criteria in the ON clauses for the joins, SELECT * FROM activities LEFT JOIN signupActivity ON activityID = SignupActivityID LEFT JOIN children ON signupActivitychildID = childrenID AND childrenID = 33 WHERE activitySection = 3 (DON'T use SELECT *, specify just those columns you need) Quote Link to comment Share on other sites More sharing options...
Darylt Posted November 7, 2015 Author Share Posted November 7, 2015 Than you Barrad. Unfortunately this hasnt worked for me. using: SELECT activityName, activityVenue, activityStart, signupActivitychildID, signupActivityStatus FROM activities LEFT JOIN signupActivity ON activityID = SignupActivityID LEFT JOIN children ON signupActivitychildID = childrenID AND signupActivitychildID = 33 WHERE activitySection = 3 has given me the result for every record in the signupActivity database related to an activity with activitySection = 3 however I dont get NULL values for the activities that child 33 hasn't signed upto Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted November 7, 2015 Solution Share Posted November 7, 2015 (edited) What if you select "childrenID" instead of "signupChildrenID"? Or SELECT * FROM activities LEFT JOIN signupActivity ON activityID = SignupActivityID AND signupActivitychildID = 33 LEFT JOIN children ON signupActivitychildID = childrenID WHERE activitySection = 3 Edited November 7, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
Darylt Posted November 7, 2015 Author Share Posted November 7, 2015 That solved it! Thank you Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.