Join three tables and show Null Values

[Darylt]

• 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

 

[Barand]

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)

[Darylt]

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
 

[Barand]

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

[Darylt]

That solved it! 

 

Thank you