pipelineconsulting
asked on
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.
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.
ASKER
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!
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!
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
> 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
>>this is not possble Unless you right a dynamic sql
short of a schema change aneeshattingal is spot on
ASKER
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?
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?
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
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
>>You can use dynamic SQL within a sproc.
or parhaps more usefully in a function returning a table.
or parhaps more usefully in a function returning a table.
>>or parhaps more usefully in a function returning a table.
sorry - non detirministic - ignore :-/
sorry - non detirministic - ignore :-/
pipelineconsulting,
Since you got 3 different solutions , and all are working, I think better you Split the points and close this Question
Since you got 3 different solutions , and all are working, I think better you Split the points and close this Question
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