-2

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.

JoshuaB
  • 29
  • 8
  • You tagged your question with both mysql and sql-server. Which is it? – Andrew Savinykh Jan 08 '16 at 21:16
  • Do YOU see an error when you run it? If you're asking for a code review there is another stack exchange site for that: http://codereview.stackexchange.com/ – Tab Alleman Jan 08 '16 at 21:22
  • If you create a view then you don't need to have a batch job to run every evening. But if you want to use a table, then you don't need to drop the table. Just delete all rows first and then insert the new rows every evening when your batch job runs. Make sure the table is created before the batch job runs for the first time. – Sunil Jan 08 '16 at 21:23
  • @TabAlleman "I was going to use" - seems like not-yet-written or even tested code, not quite a good fit for [codereview.se], which only deals with making working code better. If it doesn't work or isn't known to work yet, it's off-topic for CR. – Mathieu Guindon Jan 08 '16 at 21:24
  • Is it SQL Server database you are dealing with, or MySQL? – Sunil Jan 08 '16 at 21:28
  • Like @techspider said.. it should be a `VIEW`. That way you don't have to re-create it every evening. The view will allow you to point at the data in the actual tables, but access it all from one location (the view). – dub stylee Jan 08 '16 at 22:25
  • Thanks for the info on ""codereview" site. I will check that out. I have run the code to join the tables and that did work okay, but I have not yet run the code to make a new table. – JoshuaB Jan 11 '16 at 18:31

1 Answers1

0

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
Sunil
  • 20,653
  • 28
  • 112
  • 197
  • Thanks Sunil. Am I correct that you wrote the above as you did so that it could execute the first time without me having created the table? I know that normally I would need to have created the table before I first ran the script. This was also mentioned by you above. In the second part, I will replace * with the column names to make things concise. They only reason for that would be not having duplicate CSN and CaseID columns. Correct? – JoshuaB Jan 11 '16 at 18:40
  • @Joshua, you are welcome. Yes to both your questions. You could just include this auto table create script without you having to create a new table. Also, the `*` in the CREATE TABLE as well as the INSERT .. SELECT must be replaced by the `same list of column names` making sure that duplicate columns of CSN and CaseID are not repeated. – Sunil Jan 11 '16 at 18:50