How to select parent row only if has at least one child?

Question

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|
+----+------+

Answer 1

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)

Answer 2

SELECT p.*
FROM Parent p
WHERE EXISTS (SELECT 'X' FROM Children c WHERE c.parent = p.id);

Answer 3

Select p.id, p.text
from Parent p
inner join Children c on p.id = c.parent 
group by p.id, p.text

Answer 4

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