Snytax Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Snytax Error

Author

Topic

Tyecom
Starting Member

12 Posts

Posted - 2011-04-07 : 00:05:27

I new at SQL and is try to query multiple tables in the Northwind database. I created a backup to the "employees" table and created a query to find rows that have different values. That part seems to work ok, but I also want to query a third table "customers" also, but keep getting syntax errors. Please see syntax below:

Select EmployeeID, LastName, FirstName, City, Region, Extension

FROM dbo.Employees, dbo.Customers

WHERE NOT EXISTS

(SELECT 1 FROM dbo.Employeesbk

WHERE dbo.Employees.EmployeeID = dbo.Employeesbk.EmployeeID

AND dbo.Employees.LastName = dbo.Employeesbk.LastName

AND dbo.Employees.FirstName = dbo.Employeesbk.FirstName

AND dbo.Employees.City = dbo.Employeesbk.City

AND dbo.Employees.Region = dbo.Employeesbk.Region

AND dbo.Employees.Extension = dbo.Employeesbk.Extension

)

AND Select * from customers where customerid = 'frank'

Thank you in advance for you help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-07 : 01:11:25

You should read about
JOIN and
ALIAS NAMES
before you go on.
Another good idea is to post some sample tables, sample data and the wanted result.
And it is always a good idea to post the error message instead of "but keep getting syntax errors".

No, you're never too old to Yak'n'Roll if you're too young to die.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-07 : 04:02:39

I am not sure how you will join employee and customer tables, here is an idea
Select EmployeeID,
LastName,
FirstName,
City,
Region,
Extension
FROM dbo.Employees
INNER JOIN dbo.Customers ON dbo.Employees.EmployeeID = dbo.Custormers.EmployeeID -- If employeeId exists on both sides otherwise place column name on the basis of which these two tables can have a join
WHERE EmployeeID NOT IN ( SELECT EmployeeID
FROM dbo.Employeesbk )

AND dbo.Customers.customerid = 'frank'

--------------------------
http://connectsql.blogspot.com/

Tyecom
Starting Member

12 Posts

Posted - 2011-04-07 : 11:49:16

Thank you for responding. Sorry for not making myself clear on original post. I'll try to explain. I have three(3) tables...Employees, Employeesbk, and Customers. The below statement queries the Employees and Employeesbk tables for any kind of differences.

Select EmployeeID, LastName, FirstName, City, Region, Extension

FROM dbo.Employees

WHERE NOT EXISTS

(SELECT 1 FROM dbo.Employeesbk

WHERE dbo.Employees.EmployeeID = dbo.Employeesbk.EmployeeID

AND dbo.Employees.LastName = dbo.Employeesbk.LastName

AND dbo.Employees.FirstName = dbo.Employeesbk.FirstName

AND dbo.Employees.City = dbo.Employeesbk.City

AND dbo.Employees.Region = dbo.Employeesbk.Region

AND dbo.Employees.Extension = dbo.Employeesbk.Extension

)

This sql statement works fine, giving me my desired results. Now, I would like to add a the third table (Customers) that would allow me to return only rows that customerid = 'frank'. For example...Select * from customers where customerid = 'frank'. I would like this to be one query if possible. I really don't know the correct syntax for this query. The table structure are listed below:

Employees and Employeesbk Tables

CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[Photo] [image] NULL,
[Notes] [ntext] NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) NULL,

[dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources