468,469 Members | 2,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,469 developers. It's quick & easy.

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

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.
1 Week Ago #1
2 1318
Banfa
9,052 Expert Mod 8TB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT id, count(parent) AS children
  2. FROM yTable
  3. GROUP BY id
  4. WHERE children=1;
May do it, sorry my SQL is a bit rusty and I have no server to test it on.
1 Week Ago #2
SioSio
246 128KB
The order of execution is as follows.
FROM-> WHERE-> GROUP BY-> HAVING-> SELECT-> ORDER BY
Expand|Select|Wrap|Line Numbers
  1. 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).
6 Days Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by michael Tepperis | last post: by
1 post views Thread by Nicolae Fieraru | last post: by
2 posts views Thread by Joost Kraaijeveld | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.