?
Solved

Error in SQL Query

Posted on 2016-11-29
36
Medium Priority
?
84 Views
Last Modified: 2016-11-30
I'm trying to create an SQL query that will find unmatched records using 2 table.    Here is the query,
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE (((TblTWClean.DeptName)<>(TblADPClean.Worked Department));


The error I'm getting is Syntax error (missing operator) in query expression  "(((TblTWClean.DeptName)<>(TblADPClean.Worked Department));"
0
Comment
Question by:Mwvarner
[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
  • 9
  • 8
  • 7
  • +5
36 Comments
 
LVL 85
ID: 41905823
You have spaces in your Column names (which is a no-no, by the way):

SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE (((TblTWClean.[DeptName])<>(TblADPClean.[Worked Department]));

Note the changes in the WHERE clause
0
 
LVL 13

Expert Comment

by:Jeff Darling
ID: 41905824
Your quotes are mismatched.

I have not tested, but I suspect that is the problem.  

SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE TblTWClean.DeptName<>TblADPClean.[Worked Department];

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41905847
Well, if you are trying to find records in tblADClean which don't have matching records in tblTWClean then I would use something like:

SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code]
        , TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean
LEFT JOIN TblTWClean
ON (TblAdpClean.[EmpID] = TblTWClean.[EmpID]) AND (TblAdpClean.DeptName = TblTWClean.[Worked Department])
WHERE TblTWClean.ID IS NULL

This assumes that tblTWClean contains a PK field with the name of ID.
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905913
Your format of SQL is not good , try this. In the select you have used it properly. Basically if you have space in the column name then you should use square brackets since it is a single name just like you used in the select clause.

SELECT 
	  TblAdpClean.[Last Name]
	, TblAdpClean.[First Name]
	, TblAdpClean.[Worked Department]
	, TblAdpClean.[Pay Code]
	, TblAdpClean.Hours
	, TblAdpClean.EmpID
	, TblTWClean.DeptName
FROM 
	TblAdpClean 
	LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE TblTWClean.DeptName <> TblADPClean.[Worked Department]

Open in new window

0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41905941
The others have addressed the syntax errors but I don't believe the query will return the results you expect.

Where a <> b will not return true when b is null and so nothing should be selected.

What you want to do for a left join where you want only unmatched records is to test the right-side table pk for null

Where b Is Null
0
 

Author Comment

by:Mwvarner
ID: 41905945
Scott and Jeff, thanks for the help.  I'm running through both suggestions now and comparing the data.  Thanks for the help.  I'll let you know how it goes.
0
 

Author Comment

by:Mwvarner
ID: 41905946
Thanks Pat,

Actually I do what you suggested first and add those records to an exception table.  However it doesn't give me complete results.  That's why this second pass looking for unmatched departments seems necessary to me.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905953
try this.. I think I missed the ISNULL in the where clause..

SELECT
        TblAdpClean.[Last Name]
      , TblAdpClean.[First Name]
      , TblAdpClean.[Worked Department]
      , TblAdpClean.[Pay Code]
      , TblAdpClean.Hours
      , TblAdpClean.EmpID
      , TblTWClean.DeptName
FROM
      TblAdpClean
      LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE ISNULL(TblTWClean.DeptName,o) <> ISNULL(TblADPClean.[Worked Department],0)
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41905956
If you are also having to look for unmatched departments, you probably have a schema design issue that should be addressed.

This query should be an inner join rather than a left join.  OR you can combine the two where clauses in the original left join query so you don't have to run two queries:

Where b is Null OR where a.dept <> b.dept
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905967
or this...
SELECT 
        TblAdpClean.[Last Name]
      , TblAdpClean.[First Name]
      , TblAdpClean.[Worked Department]
      , TblAdpClean.[Pay Code]
      , TblAdpClean.Hours
      , TblAdpClean.EmpID
      , TblTWClean.DeptName
FROM 
      TblAdpClean 
      LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE TblTWClean.[EmpID] IS NULL

Open in new window

0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905982
Try this..

SELECT  
		b.[Last Name]
      , b.[First Name]
      , b.[Worked Department]
      , b.[Pay Code]
      , b.Hours
      , b.EmpID
      , c.DeptName
FROM 
(
	SELECT  
			EmpID      
	FROM 
		  TblAdpClean 
	EXCEPT
	SELECT  
			EmpID      
	FROM 
		  TblTWClean 
)k
	INNER JOIN  TblAdpClean b ON k.[EmpID] = b.[EmpID] 
	INNER JOIN TblTWClean  c ON c.[EmpID] = k.[EmpID]

Open in new window

0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41905988
Pawan, that is not Access syntax and so cannot be used against linked tables.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41906056
Thank you PatHartman!! Just moved from SQL SERVER. :)

@Author - Could you please some data and the expected output. ?
0
 

Author Comment

by:Mwvarner
ID: 41906100
I'm attaching a subset of the data in a sample database with just the tables in question.

For these two tables I'm attempting to generate an exception report where first is an employee is listed in one table but not the other.  See Orlando Ager in TblAdpClean but not in TblTWClean.

Second where the employee is in both tables but the some or all the departments don't match.

For example please see Taryn Allman.  She is listed once in TblAdpClean but she is listed 2 times in the TblTwClean.  The same goes for Rosiland Barnes.

The attached database also has the queries I've been working with.  They are close but I'm not sure I'm seeing all the exceptions.
Sample3.accdb
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906155
As other experts have suggested, there were a few issues in the original SQL statement:

1. spaces in the fields require brackets around the field.
2. the logic for the extraction must be correlated to the correct JOIN.
3. data structure may not adhere to best practices as data duplication should be avoided at all costs.

Issue #2 requires a definition of the desired results.

The last post clarified some expectations:

A. To find employees listed in one table but not the other:
Use FULL OUTER JOIN and WHERE a.key or b.key is NULL

SELECT 
	TblAdpClean.[Last Name], 
	TblAdpClean.[First Name], 
	TblAdpClean.[Worked Department], 
	TblAdpClean.[Pay Code], 
	TblAdpClean.[Hours], 
	TblAdpClean.[EmpID], 

	TblTWClean.[DeptName]
FROM 
	TblAdpClean FULL OUTER JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE 
	TblAdpClean.[EmpID] IS NULL
	OR TblTWClean.[EmpID] IS NULL

Open in new window


B. To find where employees are in both tables but have unmatched departments
USE INNER JOIN as subquery for the source table and run it against the department table. However, the OP hasn't clearly defined which table contains the departments to check against
0
 

Author Comment

by:Mwvarner
ID: 41906209
These tables are from imported data from 2 spreadsheets.  The TblAdpClean table has the field Worked Department an the TblTWClean has the DeptWorked field.

I need to create a list with any exceptions where and employee has different departments in these 2 tables as well as exceptions where an employee exists in one table but not the other.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41906347
NerdsOffTech I believe this is an Access question and Full Outer Join is not directly supported.  You have to simulate it with a union of a left and a right join
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906350
Thanks for the extra info see if the full outer join satisfies one of the two queries.

Can you post a small sample of the two tables in plain text here. That will help some experts that can't open that file
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906353
Pat is right, with access you need to do a synthetic full outer join

left join where b.id is null
Union all
right join where a.id is null
0
 

Author Comment

by:Mwvarner
ID: 41906361
When I try the outter join query I get a syntax error in From clause error.

There is the sample data.

I'm attaching 2 text files with sample data.

Thanks for all the help.
TblAdpClean.txt
TblTWClean.txt
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906409
Sorry, in Access you have to simulate a SQL FULL OUTER JOIN (with match exclusion); try this to obtain a table of employees that are listed in one table but not the other:
SELECT 
	TblAdpClean.[Last Name], 
	TblAdpClean.[First Name], 
	TblAdpClean.[Worked Department], 
	TblAdpClean.[Pay Code], 
	TblAdpClean.[Hours], 
	TblAdpClean.[EmpID], 

	TblTWClean.[DeptName]
FROM 
	TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE 
	TblTWClean.[EmpID] IS NULL
UNION ALL
SELECT 
	TblAdpClean.[Last Name], 
	TblAdpClean.[First Name], 
	TblAdpClean.[Worked Department], 
	TblAdpClean.[Pay Code], 
	TblAdpClean.[Hours], 
	TblAdpClean.[EmpID], 

	TblTWClean.[DeptName]
FROM 
	TblAdpClean RIGHT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE 
	TblAdpClean.[EmpID] IS NULL

Open in new window


Simply, the FULL OUTER JOIN (with match exclusion AKA A.key IS NULL OR B.key IS NULL) synthesis consists of:
(LEFT JOIN + B.key IS NULL) UNION ALL (RIGHT JOIN + A.key IS NULL)

SQL JOINS
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41906422
NerdsOfTech, I'm not sure where you are seeing a requirement for a full outer join.  The OP wants unmatched rows which is a simple left join.  The other set of data is where department IDs are different.  Both criteria can be applied in a query that does a left join.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906433
I think the OP said these tables are derived from spreadsheets which have similar data and the requirement for the search was to obtain a table of employees that are listed in one table but not the other.

https://www.experts-exchange.com/questions/28986084/Error-in-SQL-Query.html#a41906209
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41906514
NerdsOfTech, Please reread the thread.  Mwvarner wanted a list of records in tblA that were not in tblB.  That isn't a full outer join.  It is a left join where "b" key is null (the second picture in the left column)

Mwvarner, is your syntax error fixed.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906612
Pat I think in this case it is the bottom right scenario (full outer join excluding the matches) where it is a XOR solution AKA exclusive or; where the record is in one or the other table, but not in both tables.

Again, this is rare but the OP mentioned the two tables have similar data. I linked this direct message where the OP explains this.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41907070
Not very sure..try..

For these two tables I'm attempting to generate an exception report where first is an employee is listed in one table but not the other.  See Orlando Ager in TblAdpClean but not in TblTWClean.

1..

try..

SELECT  
		b.[Last Name]
      , b.[First Name]
      , b.[Worked Department]
      , b.[Pay Code]
      , b.Hours
      , k.EmpID
      , c.DeptName
FROM 
(
	SELECT  
		  t1.EmpID      
	FROM 
		  TblAdpClean AS t1 
	      LEFT JOIN TblTWClean t2 ON t1.EmpID = t2.EmpID
		  WHERE t2.EmpID IS NULL
) AS k
INNER JOIN  TblAdpClean b ON k.[EmpID] = b.[EmpID] 
INNER JOIN TblTWClean  c ON c.[EmpID] = k.[EmpID]

Open in new window


2. Second where the employee is in both tables but the some or all the departments don't match.


SELECT 
        TblAdpClean.[Last Name]
      , TblAdpClean.[First Name]
      , TblAdpClean.[Worked Department]
      , TblAdpClean.[Pay Code]
      , TblAdpClean.Hours
      , TblAdpClean.EmpID
      , TblTWClean.DeptName
FROM 
      TblAdpClean 
      INNER JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
      AND IIF(ISNULL(TblAdpClean.[Worked Department]),0,TblAdpClean.[Worked Department]) <> IIF(ISNULL(TblTWClean.[Worked Department]),0,TblTWClean.[Worked Department])

Open in new window


Hope it helps !!
0
 
LVL 17

Expert Comment

by:John Tsioumpris
ID: 41907123
A small amount of sample data would be most helpful....
Essentially the "filter" would be something like this
[TableA]![Field] NOT IN ( SELECT [TableB]![Field] FROM TableB)

Open in new window

0
 

Author Comment

by:Mwvarner
ID: 41907231
The first query failed with a syntax error.  See the attached file Capture1.jpg.  The second query worked after I changed the field name TblTWClean.[worked department] to TblTWClean[DeptName. in the INNER JOIN clause.  The data in the second query also looks like exactly what I need so that query is good to go.

SELECT
TblAdpClean.[Last Name],
TblAdpClean.[First Name],
TblAdpClean.[Worked Department],
TblAdpClean.[Pay Code],
TblAdpClean.Hours,
TblAdpClean.EmpID,
TblTWClean.DeptName

FROM
TblAdpClean

INNER JOIN TblTWClean ON (IIF(ISNULL(TblAdpClean.[Worked Department]),0,TblAdpClean.[Worked Department]) <> IIF(ISNULL(TblTWClean.[DeptName]),0,TblTWClean.[DeptName])) AND (TblAdpClean.[EmpID] = TblTWClean.[EmpID]);
Capture1.jpg
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 41907488
Here's the two queries you need:

Find Emp in ADP but Not In TW or in both but Depts don't match --
SELECT TblAdpClean.*, TblTWClean.*
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.EmpID = TblTWClean.EmpID
WHERE (((TblTWClean.EmpID) Is Null)) OR (((TblAdpClean.[Worked Department])<>[DeptName]));


Find Emp in TW but not in ADP --
SELECT TblTWClean.*
FROM TblTWClean LEFT JOIN TblAdpClean ON TblTWClean.EmpID = TblAdpClean.EmpID
WHERE (((TblAdpClean.EmpID) Is Null));
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41907496
Updated for case 1..

SELECT  
		b.[Last Name]
      , b.[First Name]
      , b.[Worked Department]
      , b.[Pay Code]
      , b.Hours
      , k.EmpID
      , c.DeptName
FROM 
(
	SELECT  
		  t1.EmpID      
	FROM 
		  TblAdpClean AS t1 
	      LEFT JOIN TblTWClean AS t2 ON t1.EmpID = t2.EmpID
		  WHERE t2.EmpID IS NULL
) AS k
INNER JOIN  TblAdpClean AS b ON k.[EmpID] = b.[EmpID] 
INNER JOIN TblTWClean  AS c ON c.[EmpID] = k.[EmpID]

Open in new window


Hope it helps !!
0
 
LVL 85
ID: 41907533
Pawan:

Do you test your SQL syntax before posting? Your most recent post fails, just like the last few did. The author has uploaded their database for testing - have you downloaded it to test your suggestions BEFORE posting?

Scott
0
 

Author Comment

by:Mwvarner
ID: 41907538
I did download and test before posting.  The last queries from Pat Hartman do exactly what I need. A little more testing and I think I can close this question.

Thanks'
0
 

Author Comment

by:Mwvarner
ID: 41907540
Everything works just like I need it to.  I've adapted the queries to my main database and the data looks to be exactly what I need.  It will give me an exception report for our payroll person to work from to correct the errors they have.

Thanks to everyone who assisted.

Marshall
0
 

Author Closing Comment

by:Mwvarner
ID: 41907543
Everything works just like I need it to.  I've adapted the queries to my main database and the data looks to be exactly what I need.  It will give me an exception report for our payroll person to work from to correct the errors they have.

 Thanks to everyone who assisted.

 Marshall
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41907544
Glad I could help.
0
 
LVL 85
ID: 41907677
I did download and test before posting.
My comment was not directed at you, but rather one of the Experts (Pawan). More and more we see him (and many others) rapid-posting, without testing. Tends to clutter the threads, and makes it VERY difficult to assist others.

Of course, that's just my .02 worth ...
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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