Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
JOIN line position is important |
Sun, Feb 14 2010 6:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Feb 15 2010 1:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Feb 17 2010 2:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Feb 17 2010 12:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Feb 18 2010 6:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |