How do I complete a Full Outer Join on two Select Statements with Microsoft SQL Server?

Question

I am attempting to Full Outer Join these two Select statements on Microsoft SQL Server 2014 so that I have a table that includes Date, Major Tickets, and Minor Tickets. and it says that there is a syntax issue near the "on" in the Outer Join clause. I was wondering if there is any way to resolve this issue?

Select #mytable7.Date,#mytable9.Date, #mytable7.[Major Tickets],#mytable9.[Minor Tickets] 
from 
     (select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Major Tickets] 
      from TicketCoupons
      Where PaxNo='1' 
            and EventDepart = 'DET' 
            and EventDate1 >= '20160601' 
            and EventDate1 <= '20160709'
      group by convert(VARCHAR, EventDate1, 112)) as #mytable7

      Full Outer Join

      (Select Date, [Minor Tickets] 
       from 
            (select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Minor Tickets] 
             from TicketCoupons
             Where PaxNo='1' 
                   and EventArrive = 'DET' 
                   and EventDate1 >= '20160601' 
                   and EventDate1 <= '20160709'
             group by convert(VARCHAR, EventDate1, 112)) as #mytable9 
       on #mytable7.Date = #mytable9.Date
       order by #mytable7.Date

I'm very new to SQL so if this is completely off, or not possible, I apologize in advance.

I cannot test it right now, but try taking the "#" out of your alias names. — RBarryYoung, Feb 02 '17 at 22:45
# is used by temporary tables https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx — McNets, Feb 02 '17 at 22:56
@McNets These tables (mytable9, mytable7) aren't in my database as they are just temporary names for my Select Statements. Would that mean that I should include the # in their names? — Alex, Feb 02 '17 at 23:04
Using # in alias names is fully legal, but needlessly confusing. Just leave them out. You can use whatever you want for alias names, but temporary tables all start with a #, so a casual reader will think these are involved. A subquery with an alias name is "temporary", but it's not a temporary table! Don't mix these two up. — Jeroen Mostert, Feb 02 '17 at 23:05

McNets · Accepted Answer · 2017-02-02T23:10:13.280

There is a missing parenthesis in Full Outer Join subquery:

(Select Date, [Minor Tickets] 
       from 
            (select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Minor Tickets] 
             from TicketCoupons
             Where PaxNo='1' 
                   and EventArrive = 'DET' 
                   and EventDate1 >= '20160601' 
                   and EventDate1 <= '20160709'
             group by convert(VARCHAR, EventDate1, 112))  <---- here !!
 ) as #mytable9 
 on #mytable7.Date = #mytable9.Date

Because ON is referencing mytable7 and mytable9

As Jeroen Mostert has pointet out, this query can be rewrited as simply:

Full Outer Join

        (select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Minor Tickets] 
         from TicketCoupons
         Where PaxNo='1' 
               and EventArrive = 'DET' 
               and EventDate1 >= '20160601' 
               and EventDate1 <= '20160709'
         group by convert(VARCHAR, EventDate1, 112)) as #mytable9 
   on #mytable7.Date = #mytable9.Date
   order by #mytable7.Date

Actually, the extra nesting is completely unnecessary because the outer `SELECT` simply repeats the columns of the inner `SELECT`, so it's even easier to just strip that out. — Jeroen Mostert, Feb 02 '17 at 23:06
@McNets It worked! Thank you very much for your time helping me! Really appreciate it! — Alex, Feb 02 '17 at 23:14

score 1 · Answer 2 · answered Feb 02 '17 at 23:08

Try this:

Select mytable7.Date,mytable9.Date, mytable7.[Major Tickets],mytable9.[Minor Tickets] 
from 
(
    select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Major Tickets] 
    from TicketCoupons
    Where PaxNo='1' 
        and EventDepart = 'DET' 
        and EventDate1 >= '20160601' 
        and EventDate1 <= '20160709'
    group by convert(VARCHAR, EventDate1, 112)
) as mytable7

Full Outer Join

(
    Select Date, [Minor Tickets] 
    from 
    (
        select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Minor Tickets] 
        from TicketCoupons
        Where PaxNo='1' 
            and EventArrive = 'DET' 
            and EventDate1 >= '20160601' 
            and EventDate1 <= '20160709'
        group by convert(VARCHAR, EventDate1, 112)
    ) as mytable9a
) as mytable9
on mytable7.Date = mytable9.Date
order by mytable7.Date

There were two issues..

Using # for the alias names
non matching parenthesis (so now you have a 9a and a 9 table)

Also, I find it easier to write queries using WITH - I find it makes my code much more easier to read, and to debug. Check this out:

;With MyTable7 As
(
    select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Major Tickets] 
    from TicketCoupons
    Where PaxNo='1' 
        and EventDepart = 'DET' 
        and EventDate1 >= '20160601' 
        and EventDate1 <= '20160709'
    group by convert(VARCHAR, EventDate1, 112)
),
MyTable9 As
(
    Select Date, [Minor Tickets] 
    from 
    (
        select convert(VARCHAR, EventDate1, 112) as Date, Count(FltNo1) as [Minor Tickets] 
        from TicketCoupons
        Where PaxNo='1' 
            and EventArrive = 'DET' 
            and EventDate1 >= '20160601' 
            and EventDate1 <= '20160709'
        group by convert(VARCHAR, EventDate1, 112)
    ) as MyTable9a
)
Select MyTable7.Date,MyTable9.Date, MyTable7.[Major Tickets],MyTable9.[Minor Tickets] 
From MyTable7
Full Outer Join MyTable9
    ON MyTable7.Date = MyTable9.Date
Order by MyTable7.Date

How do I complete a Full Outer Join on two Select Statements with Microsoft SQL Server?

2 Answers2