UNION TWO TABLES WITH ONLY ONE COLUMN IN COMMON

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply