[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Optional INNER JOINs using CASE or IF

Posted on 2006-04-27
15
Medium Priority
?
658 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:pipelineconsulting
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 16550982
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
 

Author Comment

by:pipelineconsulting
ID: 16551148
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16551154
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Accepted Solution

by:
Otana earned 536 total points
ID: 16551163
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
 
LVL 11

Assisted Solution

by:pootle_flump
pootle_flump earned 532 total points
ID: 16551164
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 532 total points
ID: 16551178
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16551182
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
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16551193
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
 

Author Comment

by:pipelineconsulting
ID: 16551207
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
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16551259
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
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16551265
>>You can use dynamic SQL within a sproc.
or parhaps more usefully in a function returning a table.
0
 
LVL 11

Expert Comment

by:pootle_flump
ID: 16551270
>>or parhaps more usefully in a function returning a table.
sorry - non detirministic - ignore :-/
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16552255
pipelineconsulting,
Since you got 3 different solutions , and all are working, I think better you Split the points and close this Question
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question