Jump to content

Join three tables and show Null Values


Darylt
Go to solution Solved by Barand,

Recommended Posts

  • 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

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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
 
image.png

Link to comment
Share on other sites

  • Solution

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.