dur2348
asked on
Query MS Sql database with full outer join
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
SampleData.xlsx
ASKER
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.
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.
Here's a link to the help entry for Full Outer Join in SQL Server
ms-help://MS.SQLCC.v10/MS.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.