UNION TWO TABLES WITH ONLY ONE COLUMN IN COMMON

• santa326

  SSC Eights!

  Points: 925

  More actions

  September 3, 2012 at 5:44 am

  #263639

  Hi all,

  I have two tables.

  One has info about folders and another one about files.

  Following are the table

  DECLARE@Folders TABLE( Path VARCHAR(50), Foldername Varchar(50), FolderSize INT)

  INSERT@Folders

  SELECT'C:\FOLDER1' Path, 'FOLDER1' Foldername, '3000' FolderSize UNION ALL

  SELECT'C:\FOLDER2', 'FOLDER2', '4004' UNION ALL

  SELECT'C:\FOLDER3', 'FOLDER3', '7010'

  SELECT * FROM @Folders

  DECLARE@Files TABLE( Path VARCHAR(50), Filename Varchar(50), FileSize INT)

  INSERT@Files

  SELECT'C:\FOLDER1' Path, 'File1.xlsx' Filename, '1024' Filesize UNION ALL

  SELECT'C:\FOLDER1' , 'File2.xlsx' , '300' UNION ALL

  SELECT'C:\FOLDER1' , 'File3.xlsx' , '2000' UNION ALL

  SELECT'C:\FOLDER2' , 'File4.xlsx' , '3000' UNION ALL

  SELECT'C:\FOLDER2' , 'File5.xlsx' , '200' UNION ALL

  SELECT'C:\FOLDER2' , 'File6.xlsx' , '3145' UNION ALL

  SELECT'C:\FOLDER3' , 'File7.xlsx' , '46' UNION ALL

  SELECT'C:\FOLDER3' , 'File8.xlsx' , '30' UNION ALL

  SELECT'C:\FOLDER3' , 'File9.xlsx' , '2300'

  select * from @Files

  Above two table store folder and file paths .names and sizes.

  i want to run a select statement which will give the following result.

  DECLARE@Result TABLE( Path VARCHAR(50),Foldername Varchar(50), FolderSize varchar(50), Filename Varchar(50), FileSize varchar(50))

  INSERT@Result

  SELECT'C:\FOLDER1' Path, 'FOLDER1' Foldername, '3000' FolderSize,'' Filename, '' Filesize UNION ALL

  SELECT'C:\FOLDER1' , '','','File2.xlsx' , '300' UNION ALL

  SELECT'C:\FOLDER1' , '','','File3.xlsx' , '2000' UNION ALL

  SELECT'C:\FOLDER1' , '','','File3.xlsx' , '2000' UNION ALL

  SELECT'C:\FOLDER2', 'FOLDER2', '4004','','' UNION ALL

  SELECT'C:\FOLDER2' , '','','File4.xlsx' , '3000' UNION ALL

  SELECT'C:\FOLDER2' , '','','File5.xlsx' , '200' UNION ALL

  SELECT'C:\FOLDER2' , '','','File6.xlsx' , '3145' UNION ALL

  SELECT'C:\FOLDER3', 'FOLDER3', '7010' ,'','' UNION ALL

  SELECT'C:\FOLDER3' , '','','File7.xlsx' , '46' UNION ALL

  SELECT'C:\FOLDER3' , '','','File8.xlsx' , '30' UNION ALL

  SELECT'C:\FOLDER3' , '','','File9.xlsx' , '2300'

  select * from @result

  I have tried UNION ALL But that dont seem to work as in real data i have different number of coloumns in each table.

  Regards

  Ravi T

• Ant-Green

  SSC Guru

  Points: 113504

  More actions

  September 3, 2012 at 5:57 am

  #1532930

  Based off your sample data the below will union both tables together.

  select

  fo.path,

  fo.foldername,

  CONVERT(VARCHAR,fo.foldersize) AS FolderSize,

  '' AS FileName,

  '' AS FileSize

  FROM

  @Folders fo

  union

  select

  fi.path,

  '',

  '',

  fi.Filename,

  CONVERT(VARCHAR,fi.FileSize)

  from

  @Files fi

  order by 1,4

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 3, 2012 at 6:00 am

  #1532932

  You need "placeholder" columns:

  SELECT

  [Path],

  Foldername,

  FolderSize,

  [Filename],

  FileSize

  FROM (

  SELECT

  [Path],

  [Filename] = CAST(NULL AS Varchar(50)),

  FileSize = CAST(NULL AS INT),

  Foldername,

  FolderSize

  FROM @Folders

  UNION ALL

  SELECT

  [Path],

  [Filename],

  FileSize,

  Foldername = CAST(NULL AS Varchar(50)),

  FolderSize = CAST(NULL AS INT)

  FROM @Files

  ) d

  ORDER BY [Path], Foldername DESC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• mickyT

  SSChampion

  Points: 10360

  More actions

  September 3, 2012 at 2:50 pm

  #1533064

  You could try the following query.

  select f.Path, f.FolderName, f.FolderSize, d.FileName, d.FileSize

  from @Folders f

  left outer join @Files d on f.Path = d.Path

  group by grouping sets ((f.Path, Foldername, FolderSize), (f.path, d.FileName, d.FileSize))

  order by f.Path, d.filename

  This produces the same result except there are NULLs rather than empty strings

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 4, 2012 at 1:49 am

  #1533138

  mickyT (9/3/2012)

  ---

  You could try the following query.

  select f.Path, f.FolderName, f.FolderSize, d.FileName, d.FileSize

  from @Folders f

  left outer join @Files d on f.Path = d.Path

  group by grouping sets ((f.Path, Foldername, FolderSize), (f.path, d.FileName, d.FileSize))

  order by f.Path, d.filename

  This produces the same result except there are NULLs rather than empty strings

  It does, but at a cost - each table is scanned twice and there are two sorts. The UNION version scans each table once followed by a single sort. Kudos for lateral thinking though 😎

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  September 4, 2012 at 9:18 am

  #1533351

  Hey Santa,

  Can you please validate the below code

  SELECT Path,FolderName,FolderSize,FileName,FileSize

  FROM

  (

  SELECT PATH,FolderName,FolderSize,'' AS FileName,'' AS FileSize, 1 AS ID FROM @Folders

  UNION

  select PATH,'' AS FolderName,'' as FolderSize,FileName, FileSize,2 AS ID from @Files

  )E

  ORDER BY PATH,ID

  Regards,
  Mitesh OSwal
  +918698619998