Can someone explain why if I have the join line LEFT JOIN Contacts W ON W._ID = _fkContacts as is show things work, but if I have it above the previous line (Sites join) they don't? By not work I mean that the data that should be returned from Sites is null.

SELECT
_Started,
_Left,
COALESCE(C._Name, _UnlistedCompany,'** unknown **') AS _CompanyName,
_MainPhone,
_Secretary,
_SecPhone,
RCF(_Town,RCF(_Address2,_Address1,', '),', ') AS _xLocation,
S._Switchboard AS _SitePhone
FROM Career
LEFT JOIN Companies C ON C._ID = _fkCompanies

LEFT JOIN Sites S ON S._fkCompanies = _fkCompanies AND S._SiteID = _fkSites
LEFT JOIN Contacts W ON W._ID = _fkContacts
WHERE _fkContacts = :ContactID
ORDER BY _Left DESC,_Started  DESC

Roy Lambert

Roy,

<< Can someone explain why if I have the join line LEFT JOIN Contacts W ON
W._ID = _fkContacts as is show things work, but if I have it above the
previous line (Sites join) they don't? By not work I mean that the data that
should be returned from Sites is null. >>

Do any of the upper LOJs produce NULLs ?  If so, then that will cause any
nested LOJs to not match any rows.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Can someone explain why if I have the join line LEFT JOIN Contacts W ON
>W._ID = _fkContacts as is show things work, but if I have it above the
>previous line (Sites join) they don't? By not work I mean that the data that
>should be returned from Sites is null. >>
>
>Do any of the upper LOJs produce NULLs ? If so, then that will cause any
>nested LOJs to not match any rows.

Its possible that LEFT JOIN Companies C ON C._ID = _fkCompanies will produce a NULL and if so then LEFT JOIN Sites S ON S._fkCompanies = _fkCompanies AND S._SiteID = _fkSites will also produce a NULL. The line that was moved will never produce a NULL (or if it does it means there's something wrong with the data).

So what I've done is move a line from between two lines that might produce NULLs to below them. Not sure if that stacks up with your comment.

Roy Lambert

Roy,

<< Its possible that LEFT JOIN Companies C ON C._ID = _fkCompanies will
produce a NULL and if so then LEFT JOIN Sites S ON S._fkCompanies =
_fkCompanies AND S._SiteID = _fkSites will also produce a NULL. The line
that was moved will never produce a NULL (or if it does it means there's
something wrong with the data).

So what I've done is move a line from between two lines that might produce
NULLs to below them. Not sure if that stacks up with your comment. >>

If you can send me the data and the actual SELECT statement, I can take a
look and see what is going on.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>If you can send me the data and the actual SELECT statement, I can take a
>look and see what is going on.

There's no point now - I can't get it to go "wrong" today. I haven't made any changes in the last couple of days so I have no idea why. If it starts being naughty again I shall immediately take a copy.

I hate it when it does this!

Roy Lambert