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?
 
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.
0
 
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
0
 
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!
   
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
0
 
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
0
 
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
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
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
0
 
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?
0
 
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
0
 
pootle_flumpCommented:
>>You can use dynamic SQL within a sproc.
or parhaps more usefully in a function returning a table.
0
 
pootle_flumpCommented:
>>or parhaps more usefully in a function returning a table.
sorry - non detirministic - ignore :-/
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.