March 28, 2016 at 10:33 am
Hello. My first post here and hope to get some help. It'll be obvious that I'm a newbie.
I apologize for the length of this post but I want to provide as much information as possible. I hope it's understandable. Also hope that I'm posting correctly and to correct forum.
I need some help with joins, and will appreciate any help. To make it as simple as possible,
let's say I have five tables: WorkForce (large), Sales (large), ZipTable (small), Products (small), and Skills (small).
WorkForce has fields empID and zip.
Sales has fields empID, empNAME, payID, prodID, salesTot.
ZipTable has fields zip.
Products has fields prodID, prodType.
PayTypes has fields payID, payType
I need a report that shows the following:
Column - Source or Table
empID - WorkForce. Match 'zipCode' in 'WorkForce' table to 'zipCode' in the 'ZipTable' table to get 'empID' for all reportable employees (RE).
empName - WorkForce
zipCode - WorkForce
payType - Match 'payID' in Sales table to 'payID' in 'PayTypes' table to get payType
prodType - Match 'prodID' in 'Sales' table to 'prodID' in the 'Products' table to get 'prodType'
salesTot Total - Match 'empID' in 'Sales' to 'empID' in (RE) to get total 'salesTot' by 'payType' for all 'prodID' in 'Sales' table that match 'prodID' in 'Products' table for each reportable employee.
I just can't get around syntax errors or get correct joins and need some help. Thanks for your time and help. Here's my latest attempt which gives syntax errors on 'FROM':
SELECT WorkForce.empID, WorkForce.empName, WorkForce.zip, Sales.prodID, PayTypes.payType, Products.prodType, Sum(convert(int,Sales.salesTot) AS [SumOfsalesTot]
FROM WorkForce INNER JOIN ZipTable ON WorkForce.zip = ZipTable.zip
INNER JOIN Sales INNER JOIN Products ON Sales.prodID = Products.prodID ON WorkForce.empID = Sales.empID)
INNER JOIN PayTypes ON Sales.payID = PayTypes.payID
GROUP BY WorkForce.empID, WorkForce.empName, WorkForce.zip, Sales.prodID, PayTypes.payType, Products.prodType
ORDER BY Sum(convert(int,Sales.salesTot) DESC;
March 28, 2016 at 10:46 am
Welcome to SSC. First thing I am going to suggest is that you read the first article I reference below in my signature block. It will walk you through everything you should post to get the best possible answers (yes, you may get multiple answers). What you will need to post is the DDL (CREATE TABLE statement) for the table(s) involved, some sample data (INSERT INTO statements) for each of the table(s). And most importantly the expected results based on that sample data. The best way to that is create a results table and a set of insert into statements that will show what you are looking for from your query.
March 28, 2016 at 10:50 am
You have some misplaced parenthesis. Formatting your code might make it easier to debug.
SELECT WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType,
Sum(convert(int,Sales.salesTot)) AS [SumOfsalesTot] --This wass missing a parenthesis
FROM WorkForce
INNER JOIN ZipTable ON WorkForce.zip = ZipTable.zip
INNER JOIN Sales ON WorkForce.empID = Sales.empID --You had an additional parenthesis in here.
INNER JOIN Products ON Sales.prodID = Products.prodID
INNER JOIN PayTypes ON Sales.payID = PayTypes.payID
GROUP BY WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType
ORDER BY [SumOfsalesTot] DESC; --Use column alias in your ORDER BY clause.
March 28, 2016 at 10:50 am
suggest you read this article on how to post.......makes it so much easier for all involved in helping you
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 29, 2016 at 3:23 pm
Thank you very much for your advice. The changes you suggested corrected the syntax errors but I'm still having a problem. Here is a query that produces correct results using MS ACCESS 2013 (see second Join):
SELECT WorkForce.empID, WorkForce.empName, WorkForce.zip, Sales.prodID, PayTypes.payType, Products.prodType,
Sum(Sales.salesTot) AS [SumOfsalesTot]
FROM ((WorkForce
INNER JOIN ZipTable ON WorkForce.zip=ZipTable.zip)
INNER JOIN (Sales INNER JOIN Products ON Sales.prodID=Products.prodID) ON WorkForce.empID=Sales.empID)
INNER JOIN PayTypes ON Sales.payType=PayTypes.payType
GROUP BY WorkForce.empID, WorkForce.empName, WorkForce.zip, Sales.prodID, PayTypes.payType, Products.prodType
ORDER BY Sum(Sales.salesTot) DESC;
When I execute this query in SQL Server, however, it produces only one result row.
Here is a Join construct that I used above:
FROM (((WorkForce
INNER JOIN ZipTable ON WorkForce.zip=ZipTable.zip)
INNER JOIN Sales ON WorkForce.empID = Sales.empID)
INNER JOIN Products ON Sales.prodID=Products.prodID)
INNER JOIN PayTypes ON Sales.payType=PayTypes.payType
This also works in MS ACCESS but SQL Server gives only one result row. I'm hoping that you can see something in the Join sequence or types that is causing rows to be excluded from the result. Here is briefly what I am trying to do:
1) Filter out employees from 'WorkForce' whose zip codes are not found in the 'ZipTable' table ('reportable emloyees' derived table)
2) Filter out products from 'Sales' whose prodID's are not found in the 'Products' table
3) Sum all of the sales for each employee in the 'reportabe employees' derived table
4) Use the 'PayTypes' and 'Products' tables as matching tables to display 'payType' and 'prodType' in the report instead of 'payId' and 'prodID'
Thanks again for your time and advice.
March 29, 2016 at 5:03 pm
Still need the DDL for the tables, sample data, and expected results based on the sample data.
March 29, 2016 at 8:39 pm
Thanks. I'm very new to SQL Server and want to do this right to get the help I very much need. Where is the Text Mode of Query Analyzer?
March 30, 2016 at 12:12 am
doasidont (3/29/2016)
Thanks. I'm very new to SQL Server and want to do this right to get the help I very much need. Where is the Text Mode of Query Analyzer?
I assume you mean SQL Server Management Studio? (Query Analyzer was discontinued in 2005 and though I've used it for a long time, I no longer remember where all settings were).
In SSMS, when in a query window, use menu option Query / Results to ... / Results to Text. Or hit Ctrl-T. (There is also a button on the toolbar but I will not try to describe an icon here).
Here is briefly what I am trying to do:
1) Filter out employees from 'WorkForce' whose zip codes are not found in the 'ZipTable' table ('reportable emloyees' derived table)
2) Filter out products from 'Sales' whose prodID's are not found in the 'Products' table
3) Sum all of the sales for each employee in the 'reportabe employees' derived table
4) Use the 'PayTypes' and 'Products' tables as matching tables to display 'payType' and 'prodType' in the report instead of 'payId' and 'prodID'
For tables that are only used to filter the results (only ZipTable in your case, you take columns from all other tables), I recommend using WHERE EXISTS instead of INNER JOIN. Not becuase it's more correct (they are fully equivalent in most cases), but because it makes the query a closer description of what you are trying to do. (Plus, if for whatever reason you ever get duplicates in the ZipTable, using INNER JOIN will yield unexpected results and EXISTS will just hum along happily).
March 30, 2016 at 7:39 am
Thank you. Yes, I am using SSMS. Just installed it (SS Express) a week or so ago and am learning SS as I go. Should have done so long time ago.
I've played a bit with the 'How to post data/code on a forum' instructions and think I understand how to provide some test data, although it will take a while. One question, though, is this. If I want to create a test table with say 500 records, do I have to post those 500 lines (along with other stuff), or is there a way to bundle everything as an attachment?
March 30, 2016 at 7:47 am
doasidont (3/30/2016)
Thank you. Yes, I am using SSMS. Just installed it (SS Express) a week or so ago and am learning SS as I go. Should have done so long time ago.I've played a bit with the 'How to post data/code on a forum' instructions and think I understand how to provide some test data, although it will take a while. One question, though, is this. If I want to create a test table with say 500 records, do I have to post those 500 lines (along with other stuff), or is there a way to bundle everything as an attachment?
You can attach files to your post. However, we certainly don't need 500 rows of data. We just need to get enough to cover different scenarios from your problem.
March 30, 2016 at 8:00 am
What Luis says. Create a small set of test data that illustrates the problem you are having. Anything above say a dozen rows is an indication that you either post too much data, or that your problem is of a complexity level where free help on forums will probably not suffice.
March 31, 2016 at 9:08 am
Thanks for advice. I am nearly finished with creating the scripts that will generate the test data. I have to make sure, however, that the data values are mixed enough to emulate what the live data is doing (i.e., so that some data is filtered in/out when joined, etc.). In the meantime, I have reconciled the report that is coming from the SSMS Joins that I'm using, with the report that I'm getting from both Access and Excel (using a smaller data set).
But would you please further explain, or give me an example of, your comment
For tables that are only used to filter the results (only ZipTable in your case, you take columns from all other tables), I recommend using WHERE EXISTS instead of INNER JOIN. Not becuase it's more correct (they are fully equivalent in most cases), but because it makes the query a closer description of what you are trying to do. (Plus, if for whatever reason you ever get duplicates in the ZipTable, using INNER JOIN will yield unexpected results and EXISTS will just hum along happily).
Could you give me the syntax for combining Joins and Where clauses? Like I said, I am a newbie.
Thanks.
March 31, 2016 at 9:34 am
doasidont (3/31/2016)
But would you please further explain, or give me an example of, your commentFor tables that are only used to filter the results (only ZipTable in your case, you take columns from all other tables), I recommend using WHERE EXISTS instead of INNER JOIN. Not becuase it's more correct (they are fully equivalent in most cases), but because it makes the query a closer description of what you are trying to do. (Plus, if for whatever reason you ever get duplicates in the ZipTable, using INNER JOIN will yield unexpected results and EXISTS will just hum along happily).
Could you give me the syntax for combining Joins and Where clauses?
Sure, no problem. I'll take the code Luis posted as a starting point and change the joit to ZipTables to an EXISTS:
SELECT WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType,
Sum(convert(int,Sales.salesTot)) AS [SumOfsalesTot] --This wass missing a parenthesis
FROM WorkForce
INNER JOIN Sales ON WorkForce.empID = Sales.empID --You had an additional parenthesis in here.
INNER JOIN Products ON Sales.prodID = Products.prodID
INNER JOIN PayTypes ON Sales.payID = PayTypes.payID
WHERE EXISTS
(SELECT *
FROM ZipTable
WHERE ZipTable.zip = WorkForce.zip)
GROUP BY WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType
ORDER BY [SumOfsalesTot] DESC; --Use column alias in your ORDER BY clause.
March 31, 2016 at 9:39 am
Which is equivalent to this:
SELECT WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType,
Sum(convert(int,Sales.salesTot)) AS [SumOfsalesTot] --This wass missing a parenthesis
FROM WorkForce
INNER JOIN Sales ON WorkForce.empID = Sales.empID --You had an additional parenthesis in here.
INNER JOIN Products ON Sales.prodID = Products.prodID
INNER JOIN PayTypes ON Sales.payID = PayTypes.payID
WHERE WorkForce.zip IN
(SELECT ZipTable.zip
FROM ZipTable)
GROUP BY WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType
ORDER BY [SumOfsalesTot] DESC; --Use column alias in your ORDER BY clause.
March 31, 2016 at 10:08 am
doasidont (3/31/2016)
Thanks for advice. I am nearly finished with creating the scripts that will generate the test data. I have to make sure, however, that the data values are mixed enough to emulate what the live data is doing (i.e., so that some data is filtered in/out when joined, etc.). In the meantime, I have reconciled the report that is coming from the SSMS Joins that I'm using, with the report that I'm getting from both Access and Excel (using a smaller data set).But would you please further explain, or give me an example of, your comment
For tables that are only used to filter the results (only ZipTable in your case, you take columns from all other tables), I recommend using WHERE EXISTS instead of INNER JOIN. Not becuase it's more correct (they are fully equivalent in most cases), but because it makes the query a closer description of what you are trying to do. (Plus, if for whatever reason you ever get duplicates in the ZipTable, using INNER JOIN will yield unexpected results and EXISTS will just hum along happily).
Could you give me the syntax for combining Joins and Where clauses? Like I said, I am a newbie.
Thanks.
Suggestion before you post your code, run it all in an empty database to be sure all the code runs properly.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply