Taking inner subset result of one join and combining this with an outer join

• lynhudson1

  Old Hand

  Points: 332

  More actions

  January 20, 2021 at 9:03 pm

  #3835652

  I have three tables that I would like to join, df1, df2 and df3. I would like to take the results from df1/df2 and then join this to df3.

  I would like to join tables df1 and df2 ON version and name with the conditions of the purpose

  = 'hi' or 'cat'

  I would then like to take the result of this subset and apply it to my outer join of df1

  df1 would join this inner table on host and pc

   

  df1
  
  version host date 
  pat     a16  12/1/2019 
  fam     a16  12/1/2019 
  emp     a16  12/1/2019 
  dan     a16  12/1/2019

  df2
  
  name purpose
  pat  hi
  fam  cat
  emp  dog
  dan  bird

   

  df3
  
  pc  size free date
  a16 5    1    12/1/2019
  a40 4    3    12/1/2019
  a41 3    1    12/1/2019

  **DESIRED**

  version host date      name purpose pc  size free date
  pat     a16  12/1/2019 pat  hi      a16 5    1    12/1/2019
  fam     a16  12/1/2019 fam  cat 
  

  **DOING**

  SELECT SUM(df3.size) AS size, SUM(df.3.free) AS free,
  df3.date
  FROM df3
  (SELECT DISTINCT 
  df2.purpose, df2.name,
  df1.version, df1.host
  FROM df1
  JOIN df2 ON 
  df2.name = df1.version
  WHERE df2.purpose = 'cat' OR df2.purpose = 'hi')
  AS NEW
  JOIN df3 ON 
  df3.pc = NEW.host

  However, my values are double, as there appear to be duplicates. I am still researching. Any suggestion is appreciated.

• ScottPletcher

  SSC Guru

  Points: 100946

  More actions

  January 20, 2021 at 10:42 pm

  #3835671

  You really need to provide directly usable table structures and data.  That is:

  CREATE TABLE and INSERT statement(s)

  CREATE TABLE #df1 /*or just df1*/

  ( version varchar(10) NOT NULL, host ... )

  INSERT INTO #df1 VALUES('pat', 'a16', '12/1/2019'),('fam', ...)

  CREATE TABLE #df2 ...

   

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.