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
WHEN 0 THEN 'CompaniesInfo'
WHEN 1 THEN 'CharitiesInfo'
c ON c.UserID = o.UserID
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.