?
Solved

INSERT INTO SELECT JOIN THING

Posted on 2016-11-10
2
Medium Priority
?
71 Views
Last Modified: 2016-11-11
Hi there!

I'm working in MSSQL Server 2008 R2

I have a table "AllQuarters" with a list of distinct employee numbers [EMPLOYEE] and fields for quarterly wages for those employees like so:
---------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE | Q1_GROSS_WAGES | Q2_GROSS_WAGES | Q3_GROSS_WAGES | Q4_GROSS_WAGES |
---------------------------------------------------------------------------------------------------------------------------------------------

I also have 4 tables (Q1 - Q4) that have the employee number [EMPLOYEE] and the gross wages [GROSS_WAGES] for the employee for that quarter.

I'm trying to join the Q1 - Q4 tables to the AllQuarters table on [EMPLOYEE] and insert the [GROSS_WAGES] from the appropriate quarter into its respective [QX_GROSS_WAGES] field in AllQuarters.

Not all employees exist in all Q1 - Q4 tables.  

I've tried INSERT INTO SELECT with a JOIN but my results end up with a bunch of NULL values.  Not sure what I'm doing wrong.  Any help will be greatly appreciated.  

TIA!
0
Comment
Question by:ttist25
2 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41882531
INSERT INTO AllQuarters ( EMPLOYEE, Q1_GROSS_WAGES, Q2_GROSS_WAGES, Q3_GROSS_WAGES, Q4_GROSS_WAGES )
SELECT COALESCE(Q1.EMPLOYEE, Q2.EMPLOYEE, Q3.EMPLOYEE, Q4.EMPLOYEE) AS EMPLOYEE,
    Q1.GROSS_WAGES, Q2.GROSS_WAGES, Q3.GROSS_WAGES, Q4.GROSS_WAGES  
FROM Q1
FULL OUTER JOIN Q2 ON Q2.EMPLOYEE = Q1.EMPLOYEE
FULL OUTER JOIN Q3 ON Q3.EMPLOYEE IN (Q1.EMPLOYEE, Q2.EMPLOYEE)
FULL OUTER JOIN Q4 ON Q4.EMPLOYEE IN (Q1.EMPLOYEE, Q2.EMPLOYEE, Q3.EMPLOYEE)
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 41883856
Thanks again Scott!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question