how to check same table for matched

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

how to check same table for matched

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-13 : 22:40:02

HI
I Have a table looking for a matched record.

CustomerID Docnumber Doctype
25 123456 PO
25 123456 Invoice

I need to run an SQL that will report on those where in the above case, the 'Invoice' row is not there.

This would be an 'unmatched ' condition we need to know about.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-14 : 02:52:07

So just to be clear, customerid 25 would be excluded from your report since it has a row correlated to 'Invoice'. If that's the case, I'm guessing you'd want to return rows for another customerid that has records sort of like this:

CustomerID DocNumber DocType
26 123457 PO
27 123458 PO

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-14 : 03:23:37

You can try this. If you're trying to do what I think you're doing, it should work:

declare @customer table (customerid int, docnumber int, doctype varchar(10))
insert into @customer (customerid, docnumber, doctype)
values (25,123456, 'PO'), (25,123456,'Invoice'), (26, 123457, 'PO'), (27, 123458, 'PO')

;
with PO (CustomerId, DocNumber, DocType)
AS (Select CustomerID, DocNumber, DocType
From @customer
Where doctype='PO')
,
Invoice (CustomerId, DocNumber, DocType)
AS (Select CustomerId, DocNumber, DocType
From @customer
WHERE doctype='Invoice')
,
MissingInvoice (CustomerId, DocNumber, DocType)
AS (Select PO.CustomerID, PO.DocNumber, PO.DocType
From PO
Left Outer Join Invoice I ON I.CustomerId=PO.CustomerId
Where I.CustomerId is null)

Select *
From MissingInvoice

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-14 : 12:16:59

sorry i wasn't clear.
I need those rows that have the PO as a type and then they DON"T have as well the Invoice as a type, same customerid, same docnumber.
in the example I showed this would not list as there ARE rows for the PO and Invoice as types.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-14 : 12:21:57

Adam, I'm sorry, I'm not following. Can you highlight or select the records from my sample set that you WOULD want to return on your query:

25 123456 PO
25 123456 Invoice
26 123457 PO
27 123458 PO

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-14 : 14:17:56

In this data:
25 123456 PO
25 123456 Invoice
26 123457 PO
27 123458 PO

we should see
26 123457
27 123458

as they don't have a matching Invoice type

quote:
Originally posted by flamblaster

Adam, I'm sorry, I'm not following. Can you highlight or select the records from my sample set that you WOULD want to return on your query:

25 123456 PO
25 123456 Invoice
26 123457 PO
27 123458 PO

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-14 : 14:26:19

Ok, so my query should work for your purposes then. That is the result set that my solution returns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources