Sql-server – Selecting values from one table which don’t exist in the other

ms accessquerysql server

I have two tables, one containing a list of all orders and another containing a list of orders that have been invoiced.

I have the following query which I'm testing in SQL Server Management Studio, but it's not returning the correct data. I just want to see all orders that haven't been invoiced. E.g. Any order that's not in the invoicing table. Both the Job Number and Sub Job Number are crucial. These are known as VixJobSite and VixJobSub in the invoicing table.

SELECT
VixJobSite,
VixJobSub,
dbo.JOBCOSTING_JOBS.JOB_NUMBER,
dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER,
SummaryRef,
dbo.SERVICERECORDS_CALL_OUTS.DATE_REQUIRED

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '\\nemesis\O\X\Invoicing Database\BackEnd\TestInvoicing.mdb';
    'admin';'',tblSummaryInfo),dbo.SERVICERECORDS_CALL_OUTS,dbo.JOBCOSTING_JOBS,dbo.SALES_ACCOUNTS 
WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO =       dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
and not exists
( SELECT * 
  FROM JOBCOSTING_JOBS,SERVICERECORDS_CALL_OUTS,SALES_ACCOUNTS
  WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
)

Best Answer

I think it's a matter of using EXCEPT ? Instead of NOT EXISTS()

Simple example:

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'OrderLines')
    DROP TABLE [dbo].[OrderLines];

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'InvoiceLines')
    DROP TABLE [dbo].[InvoiceLines];

-- create tables
CREATE TABLE [dbo].[OrderLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_InvoiceLines] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]
GO

-- insert sample data
INSERT INTO dbo.OrderLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(1,3),(2,1),(2,2)
INSERT INTO dbo.InvoiceLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(2,1)

-- query result missing jobnumber/subjobnumber records in second table
SELECT JobNumber, SubJobNumber
FROM dbo.OrderLines
EXCEPT
SELECT JobNumber, SubJobNumber
FROM dbo.InvoiceLines

Results:

JobNumber   SubJobNumber
----------- ------------
1           3
2           2

(2 rows affected)

I would work with the key values that make the result set (Jobnumber, Subjobnumber), and then get the other data for those rows.

Related Question