Solved

Query MS Sql database with full outer join

Posted on 2014-02-18
5
209 Views
Last Modified: 2014-09-30
We have a table in a MS Sql database named GLDetail.  I want to be able to select and total the billing and/or cost for any one job_no or group of job_no's for any particular post_prd and post_yr or group of post_prd's and post_yr's from the table.  The billing and cost for each job should display on the same line of the resulting output.  I would like to do this in a View in the database.  I have attached a sample of the data.  This data only shows the year 2014 but there are multiple years I want to select from.  Cost is made up of the following acct_no's-- Is Null , '606' , '609' , '620' , '621' , '623' , '630' , '684' , '642' , '678' , '604' , '627' , '758' , '764' , '786' , '780' , '628' and the billing is made up of following acct_no's-- Is Null Or Between '500' And '539'.  I have used Access to create queries after linking the table from MS Sql in the past, but some jobs have billing and no cost or vice versa so I believe I need full outer join which is not available in Access.  I have limited experience working in MS Sql directly.  I have attempted to create a View in MS Sql database to do this but have been unsuccessful.  I would appreciate any suggestions on how to accomplish this.
SampleData.xlsx
0
Comment
Question by:dur2348
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 39867901
To simulate a full outer join when the database engine doesn't support it explicitly, use a union  query that unions a left join query, a right join query, and an inner join query.

The union query will not be updateable so this won't work for a form.  It only works for a report.

Select * From InnerJoinQuery
Union Select * From RightJoinQuery
Union Select * From LeftJoinQuery;

Unions are sensitive to column order and data type so make sure the three queries select the identical columns in the identical order.
0
 

Author Comment

by:dur2348
ID: 39867948
Since MS Sql does support full outer join, I was hoping to be able to create View in MS Sql database that would accomplish this.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39868802
Sorry, I thought this was an Access question since that is the topic I found it under.  I don't know what MS SQL is.  Are you talking about SQL Server?


Here's a link to the help entry for Full Outer Join in SQL Server

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/58855df5-6620-479d-b662-611e94ce8e2b.htm
0
 

Author Comment

by:dur2348
ID: 39868813
Yes, I should have said MS SQL Server.  I would like to create View that displays one row per job with summed cost and summed billing for that job.  Cut and pasted link came back not matching any documents.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39870246
Here is a link to a different article with syntax and examples for all the join types.

http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

If you are working with Access, you can use the method I recommended earlier.  If you are working in some other product, you'll need to use T-SQL to create the query.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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