Q:

How do i make JOIN when need to carried out first table column values?

I have two tables rawtable and tradetable
rawtable
rawid companyname uniqueID 
1      AAA-XA         9CV
2      BBB-DEMO       10K
3      CCC-XOXO       7D

tradetable
tradeid securityname CUSIP
1       AAACOMP      9CV
2       BBBCOMP      10K

Now what I need is companyname from rawtable is bit mixed so I need to have securityname from tradetable as companyname for that I used LEFT JOIN
declare DataSourceId = 3;
SELECT DISTINCT
    @DataSourceId,
    dbo.CleanText(tradetable.securityname)
FROM    tradetable 
    LEFT JOIN
    (
        SELECT DISTINCT 
            companyname,
            uniqueID 
        FROM
            rawtable 
    ) rawtable ON tradetable.cusip =  rawtable. uniqueID 

which will give me names from tradetable but here I will miss the new not matching names from rawtable but I want those name too but in select statement 
if I use
declare DataSourceId = 3;
SELECT DISTINCT
        @DataSourceId,
        dbo.CleanText(rawtable.securityname) --instead of tradetable 

then I will select wrong mixed name 
So how can I solve this problem? or from somewhere else I need to carried out correct names as I want it like tradetable securityname
OUTPUT I EXPECTED :
rawtable
companyname uniqueID 
AAA-XA         9CV
BBB-DEMO       10K
CCC-XOXO       7D

tradetable
securityname CUSIP
AAACOMP      9CV
BBBCOMP      10K

I WANT A securityname i.e companyname in proper format for that i'm checking uniqueID
if it is matching then it will fetch securityname from tradetable
NOW 
when there is an addition record in rawtable like CCC_XOXO is there it actual name might be CCC so for taking CCC i should have to take that CCC from where it is  present in 3rd table or is there any other way? 
means i need to import that 3rd table also in my JOIN right?

A:

You mean you want to take the value from tradetable and if it doesn't exist, take it from rawtable? Like this (sqlfiddle)?
SELECT dbo.CleanText(COALESCE(t.securityname,r.companyname)) AS companyname,
    COALESCE(t.CUSIP,r.uniqueID) AS uniqueID
FROM rawtable r
LEFT OUTER JOIN tradetable t ON r.uniqueID = t.CUSIP

Output is:

COMPANYNAME     UNIQUEID
AAACOMP         CV
BBBCOMP         10K
CCC-XOXO        7D

