Menu
I have a simple one-to-many relationship. I would like to select rows from the parent only when they have at least one child. So, if there are no children, then the parent row is not returned in the result set.
Eg.
Parent:
+--+---------+
|id| text |
+--+---------+
| 1| Blah |
| 2| Blah2 |
| 3| Blah3 |
+--+---------+
Children
+--+------+-------+
|id|parent| other |
+--+------+-------+
| 1| 1 | blah |
| 2| 1 | blah2 |
| 3| 2 | blah3 |
+--+------+-------+
I want the results to be:
+----+------+
|p.id|p.text|
+----+------+
| 1 | Blah |
| 2 | Blah2|
+----+------+
581
The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
Select one or more rows and columnsSelect the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
You can do this using an EXISTS, like this:
SELECT *
FROM Parent p
WHERE EXISTS (SELECT 1
FROM Chilren c
WHERE c.Parent = p.id)
Or using a IN like this:
SELECT *
FROM Parent p
WHERE p.id IN (SELECT c.Parent
FROM Chilren c)
An inner join only returns rows that match both tables:
select distinct p.*
from Parent p
inner join Children c on c.parent = p.id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
