I think I already know the answer, but before I start creating two JOINs and writing some horrific IFs is there a way to have a conditional join?

What I'm looking for is something along these lines

select _fkSites, _fkCompanies, _fkContacts, _fkCareer, J._fksites from calls
LEFT OUTER JOIN Companies C ON Calls._fkCompanies = C._ID
LEFT OUTER JOIN Contacts W ON Calls._fkContacts = W._ID
LEFT OUTER JOIN Career J ON Calls._fkCareer = J._ID
LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
LEFT OUTER JOIN Companies P ON C._fkCompanies_Parent = P._ID
if _fkcontacts > 0 then
LEFT OUTER JOIN Sites Site ON  J._fkCompanies = Site._fkCompanies AND J._fkSites = Site._SiteID
else
LEFT OUTER JOIN Sites Site ON  _fkCompanies = Site._fkCompanies AND _fkSites = Site._SiteID


Roy Lambert

So far I've come up with

LEFT OUTER JOIN Sites Site ON  if(J._ID > 0,J._fkCompanies,_fkcompanies) = Site._fkCompanies AND if(J._ID>0,J._fkSites,_fksites) = Site._SiteID

But because its unoptimised in my test case it goes from c0.1 to c3 secs


Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:9F9215AC-2E1F-491D-B913-6C14841F2D27@news.elevatesoft.com...
>I think I already know the answer, but before I start creating two JOINs
>and writing some horrific IFs is there a way to have a conditional join?
>
> What I'm looking for is something along these lines
>
> select _fkSites, _fkCompanies, _fkContacts, _fkCareer, J._fksites from
> calls
> LEFT OUTER JOIN Companies C ON Calls._fkCompanies = C._ID
> LEFT OUTER JOIN Contacts W ON Calls._fkContacts = W._ID
> LEFT OUTER JOIN Career J ON Calls._fkCareer = J._ID
> LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
> LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
> LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
> LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
> LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
> LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
> LEFT OUTER JOIN Companies P ON C._fkCompanies_Parent = P._ID
> if _fkcontacts > 0 then
> LEFT OUTER JOIN Sites Site ON J._fkCompanies = Site._fkCompanies AND
> J._fkSites = Site._SiteID
> else
> LEFT OUTER JOIN Sites Site ON _fkCompanies = Site._fkCompanies AND
> _fkSites = Site._SiteID
>

Have you tried the IF on the select, as in

SELECT IF(_f kcontacts > 0 then Sites1._fksites else Site2._fksites)
_fksites
---
LEFT OUTER JOIN Sites Site1 on ...
LEFT OUTER JOIN Sites Site2 on ...

Robert
>
> Roy Lambert

Robert


That is the final thing. Those sort of IFs are going to get NASTY. Just so you have a rough idea here's one IF using info from the Sites table


Site._Town,
Site._County,
Site._Country,
('Site: '+
Site._SiteName +
IF(Site._Town IS NOT NULL THEN ' - ' + Site._Town ELSE '') +
IF(Site._County IS NOT NULL THEN ', ' + Site._County ELSE '') +
IF(Site._Country IS NOT NULL THEN ', ' + Site._Country ELSE '')
)AS _Location,

It's doable - I was just hoping for an easier way. I hate nested IFs especially in SQL.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:9F9215AC-2E1F-491D-B913-6C14841F2D27@news.elevatesoft.com:

> I think I already know the answer, but before I start creating two
> JOINs and writing some horrific IFs is there a way to have a
> conditional join?
>
> What I'm looking for is something along these lines
>
> select _fkSites, _fkCompanies, _fkContacts, _fkCareer, J._fksites from
> calls LEFT OUTER JOIN Companies C ON Calls._fkCompanies = C._ID
> LEFT OUTER JOIN Contacts W ON Calls._fkContacts = W._ID
> LEFT OUTER JOIN Career J ON Calls._fkCareer = J._ID
> LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
> LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
> LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
> LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
> LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
> LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
> LEFT OUTER JOIN Companies P ON C._fkCompanies_Parent = P._ID
> if _fkcontacts > 0 then
> LEFT OUTER JOIN Sites Site ON  J._fkCompanies = Site._fkCompanies AND
> J._fkSites = Site._SiteID else
> LEFT OUTER JOIN Sites Site ON  _fkCompanies = Site._fkCompanies AND
> _fkSites = Site._SiteID
>
>
> Roy Lambert

Roy,

 Perhaps you could use a UNION instead, with two identical SELECTs
except for the ending:
SELECT ...
....
LEFT OUTER JOIN Sites Site
      ON  J._fkCompanies = Site._fkCompanies
      AND J._fkSites = Site.SiteID
WHERE _fkcontacts > 0
UNION
....
SELECT ...
....
LEFT OUTER JOIN Sites Site
      ON  _fkCompanies = Site._fkCompanies AND _fkSites = Site._SiteID
WHERE _fkcontacts <= 0

(but maybe that's just what DBISAM does when we use IF anyway)
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Chris,

<< (but maybe that's just what DBISAM does when we use IF anyway) >>

Nope, it isn't that smart.

--
Tim Young
Elevate Software
www.elevatesoft.com