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

Posted on 2007-07-31
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
    LVL 9

    Accepted Solution

    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;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now