CODEWITHSUNDEEP
sqlite
ChrisJJ
ChrisJJ

Reputation: 2302

How to efficiently select all rows having parent matching criterion?

From a SQLite table having columns ID, IDofParent and Flavour, how with max. efficiency may I return the records for which any parent has Flavour = Lemon?

Upvotes: 0

Views: 41

Answers (1)

CL.
CL.

Reputation: 180210

If you have SQLite 3.8.3 or later, this can be done with a recursive common table expression:

WITH RECURSIVE LemonsAndChildren AS (
    SELECT * FROM ATable WHERE Flavour = 'Lemon'
    UNION ALL
    SELECT Child.* FROM ATable AS Child
        JOIN LemonsAndChildren AS Parent ON Child.IDofParent = Parent.ID
)
SELECT * FROM LemonsAndChildren

Upvotes: 1

Related Questions