473,288 Members | 1,705 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 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
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.
Jun 6 '21 #1
2 3973
Banfa
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
SioSio
272 256MB
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).
Jun 8 '21 #3

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

Similar topics

1
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'
1
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...
2
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...
2
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 ...
1
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...
1
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. ...
5
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...
0
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...
6
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.