19

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|
+----+------+
Matt McCormick
  • 13,041
  • 22
  • 75
  • 83

4 Answers4

27

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)
Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
  • 3
    I have not tested but I'm guessing this is slower than a join. – Hogan Apr 03 '10 at 16:22
  • 8
    @Hogan - The exists it the fastest approach. The optimizer will do the same execution plan for a null checked outer join...which will be **as** fast, but the exists is never slower. – Nick Craver Apr 03 '10 at 16:24
  • Neat. Thanks @Nick. This is true for mysql, ms sql and oracle? – Hogan Apr 04 '10 at 16:35
  • 1
    @Hogan - The exists being faster, yes that holds true...how smart the optimizer is in making an equivalent left join have the same execution plan, I'm not sure about MS SQL. – Nick Craver Apr 04 '10 at 16:42
15

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
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    this is, in my opinion, the most straightforward way to accomplish this and should be the accepted answer. – adriandz Aug 14 '13 at 17:35
3
Select p.id, p.text
from Parent p
inner join Children c on p.id = c.parent 
group by p.id, p.text
Avitus
  • 15,640
  • 6
  • 43
  • 53
1
SELECT p.*
FROM Parent p
WHERE EXISTS (SELECT 'X' FROM Children c WHERE c.parent = p.id);
João Silva
  • 89,303
  • 29
  • 152
  • 158