none
How to do full outer join with three queries RRS feed

  • Question

  • I have three queries that I need to do a full outer join on the fields Week and Year.  Only those results with matching Weeks and Years will be in the result. Do I need to create temp tables first or can I just dive in. I tried the syntax of FULL OUTER JOIN with no results.

    The queries are:


    select [Week Number][Year], 'PaidMediaImpressions', [Impressions][Impressions] * .95, 'Paid Media' as [Category], 'Tooltip' as Tooltip
    from [dbo].[vwDisplayMediaByWeek] 


    select [Week],[Year], 'AllEmailOpens' as KPIName, sum([Opens]) as KPIActual, sum([Opens]) *.95 as Goal, 'AllUp' as Category, 'Tooltip' as Tooltip
    FROM [dbo].[vwEmailAggregatesByWeek]
    where week is not null
    group by [week][year]


    select [week][Year], 'totalImpressions', [totalImpressions][totalImpressions] * .75, 'Social Media' as [Category], 'Tooltip' as Tooltip
    from [dbo].[vwSocialPaidandOrgTotalImpandEngByWeek] 

    Thursday, August 22, 2019 11:18 PM

Answers

  • Hi TWIKLE,

     

     I am sorry that I am not sure why you would like to FULL OUTER JOIN . Per your description, you want those results with matching Weeks and Years.  Would you like this one ?

     
    ;with cte1 as 
    (select [Week Number], [Year], 'PaidMediaImpressions', [Impressions], [Impressions] * .95, 'Paid Media' as [Category], 'Tooltip' as Tooltip
    from [dbo].[vwDisplayMediaByWeek] 
    ),cte2 as (
    select [Week],[Year], 'AllEmailOpens' as KPIName, sum([Opens]) as KPIActual, sum([Opens]) *.95 as Goal, 'AllUp' as Category, 'Tooltip' as Tooltip
    FROM [dbo].[vwEmailAggregatesByWeek]
    where week is not null
    group by [week], [year]
    ),cte3 as (
    select [week], [Year], 'totalImpressions', [totalImpressions], [totalImpressions] * .75, 'Social Media' as [Category], 'Tooltip' as Tooltip
    from [dbo].[vwSocialPaidandOrgTotalImpandEngByWeek] )
    select * 
    from cte1 a 
    join cte2 b on a.[Week Number]=b.[Week] 
    join cte3 c on a.[Week Number]=c.[Week] 

     

    If above sample doesn’t satisfy your requirement, please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TWIKLE Friday, August 23, 2019 4:39 PM
    Friday, August 23, 2019 2:34 AM

All replies

  • Hi TWIKLE,

     

     I am sorry that I am not sure why you would like to FULL OUTER JOIN . Per your description, you want those results with matching Weeks and Years.  Would you like this one ?

     
    ;with cte1 as 
    (select [Week Number], [Year], 'PaidMediaImpressions', [Impressions], [Impressions] * .95, 'Paid Media' as [Category], 'Tooltip' as Tooltip
    from [dbo].[vwDisplayMediaByWeek] 
    ),cte2 as (
    select [Week],[Year], 'AllEmailOpens' as KPIName, sum([Opens]) as KPIActual, sum([Opens]) *.95 as Goal, 'AllUp' as Category, 'Tooltip' as Tooltip
    FROM [dbo].[vwEmailAggregatesByWeek]
    where week is not null
    group by [week], [year]
    ),cte3 as (
    select [week], [Year], 'totalImpressions', [totalImpressions], [totalImpressions] * .75, 'Social Media' as [Category], 'Tooltip' as Tooltip
    from [dbo].[vwSocialPaidandOrgTotalImpandEngByWeek] )
    select * 
    from cte1 a 
    join cte2 b on a.[Week Number]=b.[Week] 
    join cte3 c on a.[Week Number]=c.[Week] 

     

    If above sample doesn’t satisfy your requirement, please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TWIKLE Friday, August 23, 2019 4:39 PM
    Friday, August 23, 2019 2:34 AM
  • You have a lot of basic things wrong and you also failed posted any DDL. It would've been nice if we also seen your attempt at this full outer join you talk about

    Let's start with the basics. 
    Columns are nothing whatsoever like fields. If you ever get around to reading the SQL standards or a good book on the language, will find that a field is part of a column. 
    .
    "Year" is both a reserved word and vague, so it cannot be a column name. Both "week" and "year "are units of measure on a temporal scale, not attributes by themselves. They also cannot be split up. Read the ISO 8601 standards about the week-within-year date format. The week does not float out there all by itself

    There is no such thing as a generic "category"; this violates the ISO 11179 naming rules as well as common sense. Data element names should generally be in the format of "<attribute>_<attribute property>".

    Putting prefixes like "VW_" or "TBL_" on a table name is a design flaw called the tibble and it is a source of great amusement. It says that you don't know how to separate data and metadata when designing a schema. You did a Volkswagen! 

    SQL is based on a tiered architecture. We don't format column names or things like that for display purposes in the database tier; that's done in a presentation layer in your system. And so would those percantage calculations. SQL is the database language, not a report writer

    My guess that you want to go to the base tables from which your "Volkswagens" were created, and do the calculations. As a really bare-bones skeleton it might look something like this:

    SELECT foobar_week,
                  SUM (CASE WHEN media_category = 'paid_media_impressions' THEN .. ELSE 0.00 END)
                      AS paid_media_impressions_tot,
                 SUM (CASE WHEN media_category =  'all_email_opens'  THEN .. ELSE 0.00 END)
                      AS  email_opens_tot,
                SUM (CASE WHEN media_category =  'social media' THEN .. ELSE 0.00 END)
                      AS  email_opens_tot
    FROM Weekly_Display_Media
    GROUP BY foobar_week; 

    As I said, without any specs or DDL, this is just a guess. Essentially I think that you've taken views and tried to work around them instead of just discarding them and starting over when it's a new query.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 23, 2019 1:24 PM
  • Thank you. This worked.
    Friday, August 23, 2019 4:39 PM