Joining Two Temp Tables

Question

I would like to join two temp tables into one big temp table. I want all of the records from my first temp table and want the records from my second temp table ONLY if the ProductID doesn't exist in my first temp table.

First temp table:

--define temporary table
declare @tmp table (
      ManagerID int null,
      ManagerName varchar(250),
      ProductID int null,
      ProductName varchar(250),
      RFIFixedIncomeAttributionID int null,
      Value decimal(8,4) null,
      Name varchar(250),
      Sector varchar(250)
      )

--populate temp table
insert into @tmp

select 
m.ManagerID, m.ManagerName, p.ID as 'ProductID', p.ProductName, sa.RFIFixedIncomeAttributionID, sa.Value, sc.Name,

case when gm.GeographicMandateID = 2 and s1.SubType1ID = 10 and s2.SubType2ID = 39 then 'Core'
    when gm.GeographicMandateID = 2 and s1.SubType1ID = 10 and s2.SubType2ID = 38 then 'Intermediate'
end as 'Sector'

from Products p

join Managers m on m.ManagerID = p.ManagerID
left join RFIFixedIncomeAttribution fia on fia.ParentID = p.ID and fia.ParentTypeID = 26
left join RFIFixedIncomeSectorAllocation sa on sa.RFIFixedIncomeAttributionID = fia.ID
    and sa.RFIFixedIncomeDataTypeID = 1
join RFIFixedIncomeSectorCategories sc on sc.ID = sa.RFIFixedIncomeSectorCategoryID
join SubType1 s1 on s1.SubType1ID = p.SubType1ID
join SubType2 s2 on s2.SubType2ID = p.SubType2ID
join GeographicMandates gm on gm.GeographicMandateID = p.GeographicMandateID

where p.prodclasscategoryid = 4
and fia.year = 2014
and fia.quarter = 6/3
and p.Rank = 1

order by m.ManagerName, p.ProductName

--get filtered dataset
select * from @tmp 
where 
Sector in ('Core')

Second temp table:

--define temporary table
declare @tmp2 table (
      ManagerID int null,
      ManagerName varchar(250),
      ProductID int null,
      ProductName varchar(250),
      RFIFixedIncomeAttributionID int null,
      Value decimal(8,4) null,
      Name varchar(250),
      Sector varchar(250)
      )

--populate temp table
insert into @tmp2

select 
m.ManagerID, m.ManagerName, p.ID as 'ProductID', p.ProductName, sa.RFIFixedIncomeAttributionID, sa.Value, sc.Name,

case when gm.GeographicMandateID = 2 and s1.SubType1ID = 10 and s2.SubType2ID = 39 then 'Core'
    when gm.GeographicMandateID = 2 and s1.SubType1ID = 10 and s2.SubType2ID = 38 then 'Intermediate'
end as 'Sector'

from Products p

join Managers m on m.ManagerID = p.ManagerID
join Vehicles v on v.ProductID = p.ID
join ManagerAccounts ma on ma.VehicleID = v.ID
join Accounts a on a.MgrAccountID = ma.MgrAccountID
left join RFIFixedIncomeAttribution fia on fia.ParentID = a.AccountID and fia.ParentTypeID = 6
left join RFIFixedIncomeSectorAllocation sa on sa.RFIFixedIncomeAttributionID = fia.ID
    and sa.RFIFixedIncomeDataTypeID = 1
join RFIFixedIncomeSectorCategories sc on sc.ID = sa.RFIFixedIncomeSectorCategoryID
join SubType1 s1 on s1.SubType1ID = p.SubType1ID
join SubType2 s2 on s2.SubType2ID = p.SubType2ID
join GeographicMandates gm on gm.GeographicMandateID = p.GeographicMandateID

where p.prodclasscategoryid = 4
and fia.year = 2014
and fia.quarter = 6/3
and p.Rank = 1

order by m.ManagerName, p.ProductName

--get filtered dataset
select * from @tmp2 
where 
Sector in ('Core')

Answer 1

This is a case of "find all rows with NO MATCH in the other table" and we have a pattern for that. First, swap your tables - the table where you expect to be missing rows will be the second or RIGHT table, the other is the LEFT table.

select <columns>
from table1
LEFT OUTER JOIN table1.ID = table2.ID
where table2.ID IS NULL

OR... don't swap the tables and use RIGHT OUTER JOIN - but that's non-standard.

In your code, there's a problem...

and fia.quarter = 6/3

is equivalent to:

and fia.quarter = 2

I think you need some quotation marks there.

Answer 2

Few points that have already brought up

• Union is the term you want, join is something quite different.

• You are not working with temp tables, you are working with table variables. Not quite the same thing

• mysql and mssql are not the same thing, tag your questions as one or the other, not both.

  select * from @tmp
  union all
  select * from @tmp2 where productID not in (select productID from @tmp)
  

Not sure if I'd rely on this query in MySQL as it'll struggle with the not in clause...you can use the join syntax in Jasmine's answer for the second half of the union clause.