Optional INNER JOINs using CASE or IF

Hi,

I have a table which stores organisation information. For arguments sake, the organisation may either be a company or charity, and this is stored in a smallint column where a company = 0, a charity = 1. (NB In the future there may be more types of organisation stored in this table, hence smallint rather than tinyint)

The Organisation table only stores general information about hte organisation (e.g. the name), and I have two other tables, CompaniesInfo and CharitiesInfo which stores the specific information particular to each type (e.g. company numbers and charity numbers)

I want to be able to select the Organisations table through a sproc and conditionally join either the CompaniesInfo table or CharitiesInfo table dependent on the value of the OrganisationType column in the Organisations table.

So.... I have a statement as follows:


SELECT * FROM Organisations o

     INNER JOIN

          CASE o.OrganisationType

                WHEN 0 THEN 'CompaniesInfo'
                WHEN 1 THEN 'CharitiesInfo'

          END

          c ON c.UserID = o.UserID

RETURN

This clearly doesn't work, but I've no idea whether I could do this better using IF statments or a variation on the above.
pipelineconsultingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OtanaCommented:
SELECT
o.* ,
case o.OrganisationType when 0 then co.Field1 when 1 then ch.Field1 end as [Field1],
case o.OrganisationType when 0 then co.Field2 when 1 then ch.Field2 end as [Field2]
FROM Organisations o
LEFT JOIN CompaniesInfo co on co.UserID = o.UserID
LEFT JOIN CharitiesInfo ch on ch.UserID = o.UserID
pipelineconsultingAuthor Commented:
Not quite what I was looking for - this just returns all the tables joined together. Was hoping to join either one table or another rather than having one huge table (if, in the future, there were 6 types of organisation, this would be 7 joined tables which is a bit unwieldy!)

In pseudo-sql this would be a bit like

SELECT * FROM Organisations o

    IF o.OrganisationType = 0 THEN INNER JOIN Companies...
    IF o.OrganisationType = 1 THEN INNER JOIN Charities...

WHERE o.UserID = @UserID


BTW I forgot to mention the @UserID parameter to return just 1 record rather than the whole lot!
   
Aneesh RetnakaranDatabase AdministratorCommented:
pipelineconsulting,
> SELECT * FROM Organisations o

>      INNER JOIN

>           CASE o.OrganisationType

>                 WHEN 0 THEN 'CompaniesInfo'
>                 WHEN 1 THEN 'CharitiesInfo'

>           END

>           c ON c.UserID = o.UserID

> RETURN


this is not possble Unless you right a dynamic sql
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

OtanaCommented:
In that case I think you'll have to work with IF:

declare @OrgType int

select @OrgType = OrganisationType from Organisations where UserID = @UserID

IF @OrgType = 0
  BEGIN
    SELECT * FROM Organisations o
    INNER JOIN Companies c ON c.UserID = o.UserID
    WHERE o.UserID = @UserID
  END
IF @OrgType = 1
...

and so on.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pootle_flumpCommented:
Hi

It really depends on what the further organisations are likely to be however you may want to consider a single organisation table with a flag to indicate it's type. Obviously this can only work if there aren't too many attributes unique to each organisation type. If you really think that TinyInt is not likely to be sufficient for the number of orgnisations you are likely to eventually have then think about scaling the above query for 257+ organisations\ tables. I would really think hard about this now if I were you. There are other laternatives too but they aren't too easy to handle either.

HTH
Aneesh RetnakaranDatabase AdministratorCommented:
this will do it for you

SELECT *
FROM Organisations o
INNER JOIN CharitiesInfo c ON c.UserID = o.UserID
WHERE OrganisationType = 1
UNION ALL

SELECT *
FROM Organisations o
INNER JOIN CompaniesInfo c ON c.UserID = o.UserID
WHERE OrganisationType = 0
Aneesh RetnakaranDatabase AdministratorCommented:
Oh.. I forgot to put the alias


SELECT *
FROM Organisations o
INNER JOIN CharitiesInfo c ON c.UserID = o.UserID
WHERE o.OrganisationType = 1

UNION ALL

SELECT *
FROM Organisations o
INNER JOIN CompaniesInfo c ON c.UserID = o.UserID
WHERE o.OrganisationType = 0
pootle_flumpCommented:
I think it is easy to come up with an answer given the current situation however if you scale up the number of entities:

>>this is not possble Unless you right a dynamic sql

short of a schema change aneeshattingal is spot on
pipelineconsultingAuthor Commented:
Cheers Pootle_Flump (great name lol!), only likely to be three or four different org types, but they attributes for each are very different, hence why they are split into different tables, and I don't really like things hanging around in retrieved tables if they don't need to be there. :)

Tinyint will only handle 0 or 1 right?

Thanks to everyone else, I am averse to using Dynamic SQL as I would prefer to keep the sql separate from my .NET app.

Guess either aneeshattingal and otana's solutions are both valid...sooo how are points best split?
pootle_flumpCommented:
TY :-)

TinyInt = 0 to 256
SmallInt = -33000 to 33000 (and a bit  - I always forget...)
Bit = 0 or 1

You can use dynamic SQL within a sproc.

If you are only going to have up to four then fair enough. I would create a single view incorporating this logic and use that for any subsequent sprocs\ views that need to access this data. You will also need to bear in mind that there will be a performance hit for this type of design (mitigated if you go for dynamic SQL I would expect).

HTH
pootle_flumpCommented:
>>You can use dynamic SQL within a sproc.
or parhaps more usefully in a function returning a table.
pootle_flumpCommented:
>>or parhaps more usefully in a function returning a table.
sorry - non detirministic - ignore :-/
Aneesh RetnakaranDatabase AdministratorCommented:
pipelineconsulting,
Since you got 3 different solutions , and all are working, I think better you Split the points and close this Question
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.