locked
need query to flatten out three simple tables RRS feed

  • Question

  • User1868852945 posted

    How can I flatten out data from three tables into one result set that would include all the data?

    This sets up three simple tables with data for the purpose of working this problem.

    USE [db1]

    DROP TABLE [dbo].[tbl1]
    DROP TABLE [dbo].[tbl2]
    DROP TABLE [dbo].[tbl3]

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[tbl1](
    [id] [smallint] IDENTITY(1,1) NOT NULL,
    [col1] [nchar](10) NULL
    ) ON [PRIMARY]
    GO
    insert into tbl1(col1) values ('a')
    insert into tbl1(col1) values ('b')
    insert into tbl1(col1) values ('c')
    insert into tbl1(col1) values ('d')
    insert into tbl1(col1) values ('e')

    CREATE TABLE [dbo].[tbl2](
    [id] [smallint] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL
    ) ON [PRIMARY]
    GO
    insert into tbl2(col2) values ('aa')
    insert into tbl2(col2) values ('bb')
    insert into tbl2(col2) values ('cc')

    CREATE TABLE [dbo].[tbl3](
    [id] [smallint] IDENTITY(1,1) NOT NULL,
    [col3] [nchar](10) NULL
    ) ON [PRIMARY]
    GO
    insert into tbl3(col3) values ('apple')
    insert into tbl3(col3) values ('banana')

    The id's all have the same value for their respective row number so this query will flatten the data by using left outer joins

    select a.col1,
    b.col2,
    c.col3
    from tbl1 a left outer join
    tbl2 b
    on (a.id = b.id) left outer join
    tbl3 c
    on a.id = c.id

    The problem with this query is that the table with the highest number of rows must appear first, then the next hiherst and next highest, etc.
    I need all the data to appear in the flattened result but if tbl3 had 6 fruits, then the sixth fruit would not be included.

    I do not want to get row count for each table and dynamically build the query which is the only way I can see would solve this problem.
    How can I flatten in a way that included all the data? Thanks.

    Tuesday, November 14, 2017 5:17 AM

Answers

  • User-595703101 posted

    Hello gunderj,

    Please check following SQL CTE Select statement where I had to use a SQL numbers table function to eliminate the problem that occurs when tbl1 has less rows than tbl3 or tbl2 as you have mentioned in your post

    The main table in FROM clause is the numbers table, so I an guarantee that it is a match for every  row in other 3 tables

    Please check the numbers table function I had referenced as a sample. You can use any other you can find on the web

    ;with maxids as (
    	select max(id) id from tbl1
    	union all
    	select max(id) id from tbl2
    	union all
    	select max(id) id from tbl3
    ), numberstbl as (
    	SELECT * FROM dbo.NumbersTable(1,(select max(id) from maxids),1)
    )
    select 
    	a.col1,
    	b.col2,
    	c.col3
    from numberstbl
    left outer join tbl1 a 
    	on i = a.id 
    left outer join tbl2 b
    	on i = b.id 
    left outer join tbl3 c
    	on i = c.id
    

    Output of the above query will be as follows

    col1    col2    col3
    a             aa            apple     
    b             bb            banana    
    c             cc            pear      
    d             NULL    watermelon
    e             NULL    cherry    
    NULL    NULL    apricot  

    I hope this is what you want

    Best Regards,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 14, 2017 3:36 PM