Join 3 tables INTO 1 new table

Question

Table A = Clarity (uniqe ID = CaseID; contains CSN)

Table B = Survey (unique ID = CSN)

Table C = QA (unique ID = caseID; contains CSN)

Table D = FullData

Goal:

“TableD” contains:

all “TableC”,

all “TableA” for which there is a “CaseID” and “CSN” in common with “Table C”

all “TableB” for which there is a “CSN” in common with “Table C”

Table is remade every evening. There are a lot of people who will be doing query research on “Table D.” I think it needs to be a table and not a view.

I was going to use:

Create TableD AS    
    Select *    
    From TableC    
    Left Join TableA    
      ON  TableA.CaseID = TableC.CaseID AND TableA.CSN = TableC.CSN    
    Left Join TableB    
      ON TableC.CSN = TableC.CSN

I was going to use SQL Agent to make the script run every night. This seems too simple. Do I have to drop the table that was made the day before? Does anyone see an error? I am using Microsoft SQL Server.

Answer 1

I am assuming you have a SQL Server database.

The table approach might have an advantage because you said that many people would be using it for research/report purpose. You don't want to bog down your main application tables due to many query requests for reporting/research. Its best to have a separate reporting table so that your main application tables are unaffected by the extra traffic for reporting/research purpose.

If you want to use a table approach, then following script can be used. I would recommend against using * in SELECT * since there are duplicate columns across the tables you are using like CaseID and CSN; instead mention a list of columns that you want in your tableD.

IF Object_id(N'TableD', N'U') IS NULL 
BEGIN 
  SELECT    * 
  INTO      tabled 
  FROM      tablec 
  LEFT JOIN tablea 
  ON        tablea.caseid = tablec.caseid 
  AND       tablea.csn = tablec.csn 
  LEFT JOIN tableb 
  ON        tablec.csn = tablec.csn; 

END 
ELSE 
BEGIN 
  DELETE 
  FROM   tabled;       
  INSERT INTO tabled
  SELECT    * 
     FROM      tablec 
  LEFT JOIN tablea 
  ON        tablea.caseid = tablec.caseid 
  AND       tablea.csn = tablec.csn 
  LEFT JOIN tableb 
  ON        tablec.csn = tablec.csn; 

END