How to find those records have no parent and child in same table?

[Zeeshan Ahmad]

i have the following table:

CREATE TABLE dbo.Invoices ( [InvoiceCode] varchar(20), [ParentInvoiceCode] varchar(20),
[InvoiceDate] date )
INSERT INTO dbo.Invoices
VALUES
( 'INV-001', 'INV-001', N'2015-01-01 00:00:00.000' ),
( 'INV-002', 'INV-001', N'2015-01-01 00:00:00.000' ),
( 'INV-003', 'INV-001', N'2015-01-01 00:00:00.000' ),
( 'INV-004', 'INV-004', N'2015-01-01 00:00:00.000' ),
( 'INV-005', 'INV-005', N'2015-01-01 00:00:00.000' ),
( 'INV-006', 'INV-006', N'2015-01-01 00:00:00.000' ),
( 'INV-007', 'INV-007', N'2015-01-01 00:00:00.000' ),
( 'INV-008', 'INV-007', N'2015-01-01 00:00:00.000' ),
( 'INV-009', 'INV-007', N'2015-01-01 00:00:00.000' ),
( 'INV-010', 'INV-007', N'2015-01-01 00:00:00.000' )


as you can see that record number 4,5,6 have no parent and child.
what will be the query to show these records only.

[Banfa]

It is more like they are their own parent/child, the records you are looking for are the ones that only have 1 child, themselves.

Lets assume your table is called yTable, the first field is called id and the second field is called parent then

SELECT id, count(parent) AS children
FROM yTable
GROUP BY id
WHERE children=1;

May do it, sorry my SQL is a bit rusty and I have no server to test it on.

[SioSio]

The order of execution is as follows.
FROM-> WHERE-> GROUP BY-> HAVING-> SELECT-> ORDER BY

SELECT * FROM `Invoices` GROUP BY `ParentInvoiceCode` HAVING (Count(`ParentInvoiceCode`))=1;

The "GROUP BY" clause is executed after "FROM".
Group by `ParentInvoiceCode` in "GROUP BY".
The result is extracted under the condition specified in "HAVING" (in this case, (COUNT(`ParentInvoiceCode`)) = 1).