472,989 Members | 2,679 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,989 software developers and data experts.

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
( '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.
Jun 6 '21 #1
2 3718
9,065 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.
Jun 7 '21 #2
272 256MB
The order of execution is as follows.
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).
Jun 8 '21 #3

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

Similar topics

by: michael Tepperis | last post by:
the following SQL is not acceepted by DB2 V7: INSERT INTO BLT15 VALUES SELECT A.TNR , A.LNR , A.LINDEX , 'P'
by: Nicolae Fieraru | last post by:
Hi All, I have a table with the following information: ID, Name, Address, Field4 In this table it happens to have duplicate records with identical Name and Address but different content in...
by: mkidd | last post by:
Hi, I'm new at vb .net and I have an issue that must be a common one, but I haven't found an example of it yet. I want to create parent-child relationships for 3 tables...
by: Joost Kraaijeveld | last post by:
I have a table with column1, column2, column3 and column4. How do I get all records, sorted by column4 that have the same column1,column2 and column3? TIA Joost ...
by: AA Arens | last post by:
On the main form I choosed a position (like manager, secretary...) from a combo box. The field in the footer form of another form mentioned the ID of the position. How to have the position TEXT...
by: Rdailey | last post by:
Hello, I am trying to compare record values in one column to the next column within the same table. I have tried everything that I can think of and still have not achieved the desired result. ...
by: binky | last post by:
Question for all you SQL gurus out there. I have a (seemingly) complex query to write, and since I'm just learning SQL server, am not sure how to go about it. Tables: t_trans t_account All...
by: Deano | last post by:
I have a feature where the user is allowed to make a copy of their data. Some of these tables are related to a child table. From what I can see the easiest way to do this is to copy to a temp...
by: A1M6K | last post by:
Very new to VBA and struggling to link the main form and the subform to the same record in the same table. Currently they are each entering separate records in the same table. They are linked by...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.