can someone help me with a left join - what is MySQL thinking?

sfullman

I have 4 tables:

children
----------
id

ChildrenFosterhomes
---------
id
Children_ID
Fosterhomes_ID

ChildrenCarelevels
-----------------------
id
Children_ID
Carelevels_ID

AssignmentNotes
----------------------
ChildrenFosterhomes_ID
ChildrenCarelevels_ID
DateOfReport
/* note the above 3 are a primary key */
Notes

I have shown only the key fields.

this query fails

SELECT
[fields]
FROM
children a,
ChildrenFosterhomes b,
ChildrenCarelevels c
LEFT JOIN AssignmentNotes d ON 
b.ID=d.ChildrenFosterhomes_ID AND
c.ID=d.ChildrenCarelevels_ID AND
d.DateOfReport='2011-04-01' /* date is a PHP variable */

WHERE a.ID=b.Children_ID AND a.ID=b.Children_ID

by saying "Unknown column b.ID in ON Clause"

I somewhat understand that d is joined to c and NOT b and somehow can't see b, HOWEVER, because of the nature of table d's primary key, I need BOTH b.ID AND c.ID to match up to exclusively join a note to the other tables.

So my questions (in order of priority):

if you understand how I could write the query equivalently, could you please show me that, THANKS
can you explain to me why my query is failing. I SOMEWHAT understand it, but not enough to avoid still doing it from time to time, or obviously avoiding WANTING to do it 🙂

thanks,
Samuel

bradgrafelman

Remove all of the conditions in the ON condition that don't relate table b to table d (so all but the first). Move those conditions to the WHERE clause (or, alternatively, rewrite all of the joins to use the '<JOIN_TYPE> JOIN' syntax instead of the comma).

sfullman

bradgrafelman;10979211 wrote:
Remove all of the conditions in the ON condition that don't relate table b to table d (so all but the first). Move those conditions to the WHERE clause (or, alternatively, rewrite all of the joins to use the '<JOIN_TYPE> JOIN' syntax instead of the comma).

But my whole problem is, I NEED to reference both table b and c since the primary KEY in table d includes those references.

also, IF I move the other conditions to the where clause, the problem is, that if a cross-product record between b and c doesn't have a note for that date, it will not exist in the resultset - so I can't do that.

Can you show me the query you're thinking of using the <JOIN_TYPE> JOIN? I think an example would enlighten me on how to do this and would solve the dilemma..

Thanks

johanafm

1.
I don't really get what you want your data structure to represent, but assuming things are as they should be, I'm guessing what you want is

SELECT
[fields]
FROM
child c
INNER JOIN child_fosterhome cf ON cf.child_id = c.id
INNER JOIN child_carelevel cc ON cc.child_id = c.id
LEFT JOIN assignment_note an ON an.carelevel_id = cc.id AND an.fosterhome_id = cf.id
AND an.date_of_report='2011-04-01'

Is it perhaps simply a matter of being on a case sensitive MySql / OS combination (i.e. non-windows)? Sometimes you call an identifier 'id', other places reference the same identifier as ID.
Wether this is the actual problem or not I recommend that you stick to lowercase letters and use underscores rather than camel casing when it comes to MySQL since it's case sensitive on some systems but not others. Should you ever move your db from Windows to another OS you risk having your code suddenly break for no good reason.

You should probably also note that you reference b.Children_ID twice in the where clause. Should the second one be table c?

And here are my thoughts about your schema. It's entirely possible that I've missunderstood things since I don't have a working knowledge of what you are doing. So read and consider this, or disregard it entirely. It's up to you 🙂

What's id? And what's Fosterhomes_ID? Assuming this is a link table between child and fosterhome, there is no reason for the separate id named 'id' since: PRIMARY KEY (child_id, fosterhome_id) does the trick just fine and you don't really need to automatically generate keys in this table.
But perhaps more importantly, can a child really live in several fosterhomes? Else, why not just have a fosterhome_id in the Child table?

Can there be different carelevels for one single child? Else, the exact same reasoning applies here. I.e. remove link table, put carelevel in child table.

Does the assignment note regard only the fosterhome, or does it regard the fosterhome for one specific child? If it's the latter, you should have the child_id as part of the PK. Also, unless the child can live in more than one fosterhome at the same time, this means you can drop the fosterhome_id from the PK.
Unless one child can have several carelevels in place at the same time, there is no need for carelevel to be part of the PK either.