[Last Call] Learn how to a build a cloud-first strategyRegister Now


How to rewrite a query to find records that exist on 1 table and not on another table ?

Posted on 2007-07-31
Medium Priority
Last Modified: 2010-03-20
I am developing an application in Access using Access as the front end and SQL Server as the back end

The following query tells me the number of records where the CustomerNumber + OfficeNumber match
between 2 tables named tblCustomers and tblProducts:

select count(*)
FROM tblCustomersNew As C inner JOIN tblProductsNew As P
ON C.CustomerNumber=P.CustomerNumber AND C.OfficeNumber=P.OfficeNumber

Could you tell me how to rewrite this query to find any record(s) on the
tblProductsNew table and not on the tblCustomersNew table ?
Question by:zimmer9
1 Comment

Accepted Solution

Rurne earned 2000 total points
ID: 19605308
Use an outer join, then check for empty halves the outer join:

select count(*)
FROM tblCustomersNew As C RIGHT JOIN tblProductsNew As P
ON C.CustomerNumber=P.CustomerNumber AND C.OfficeNumber=P.OfficeNumber WHERE C.CustomerNumber IS NULL;

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 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