Join table with Else Clause

[cojiro]

I'm trying to create a support table that will be used to append a description field to rows in my main table. An example of what I'm talking about is below...

Main table
COMPANY | AMOUNT
-----------------
Comp1 100
Comp2 200
Comp3 300

Support table
COMPANY | DESCRIPTION
--------------------------------------
Comp1 "Company one"
(everything else) "All Others"

Upon joining these tables by company I would like to get the following results:
COMPANY | AMOUNT | DESCRIPTION
---------------------------------------------
Comp1 100 Company one
Comp2 200 All Others
Comp3 300 All Others


Does anyone know how to get this kind of functionality out of joining tables, or will I need to do something more complicated... Any advice is much appreciated!

 

[AlexCuse]

Check in Books online for CASE function. It will go something like this (replace myTable with your table name)

select company
, amount
[b], case company
when 'Comp1' then 'Company One'
else 'All Others' end
as Description[/b]
from MyTable

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Of course, this does not use a join. But a join and support table are not necessary here. If where you put (everything else) you have actual company names, you could try this:

select a.company
, a.amount
, b.Description
from Maintable a
inner join SupportTable b
on a.company = b.company

Of course, for this you need to make absolutely certain that your 'support table' is updated every time a new company is added to your 'main table'.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[cojiro]

Thanks for your help Alex. I would use the first solution, except that this is just a simple example of a much more complicated problem. So much so that I wanted to use the table join so that maintaining this would be easier.

The second solution won't work because I don't know all of the possibilities of "Company".

I'm starting to think I should just do a left join on the support table, then after that appending a default description for all where the "description=is null".

Thanks again.

 

[AlexCuse]

Changing it to a left join will be easy enough.

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Left join, for sure. Most left joins look like this...

[tt][blue]
Select Main.Company,
Main.Amount,
Support.Description
From Main
Left Join Support
On Main.Company = Support.Company
[/blue][/tt]

If there is no matching record in the support table, NULL will be returned for the description column. A simple IsNull replacement will allow you to specify a default value to return when there is not match, like this...

Select Main.Company,
       Main.Amount,
       [!]IsNull([/!]Support.Description[!], 'All Others') As Description[/!]
From   Main
       Left Join Support
         On Main.Company = Support.Company

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cojiro]

Thanks George, that is slick. I'll definitely do that.

 

[AlexCuse]

Or, if you want to identify records where description is null, to get them into support table this:

(I am not sure if you mean updating source table or doing what george has pointed out when you say 'appending default description).

Note that it's

where description is null

not

where description = is null

Select Main.Company,
       Main.Amount,
       Support.Description
From   Main
       Left Join Support
         On Main.Company = Support.Company
Where  Support.Description is null

Ignorance of certain subjects is a great part of wisdom