What's the relationship between parents and children (Isn't that the universal question?)
If parents can have many children and children can have many parents, is there a M:M table to link them?
If a child record has a single ParentFK, that's kinda sad. If you want Parents that have *ALL FOUR* of those children, then a 4-times join is the most likely simplest and easiest to read and maintain later:
SELECT * FROM Parent join Child Child1 on ParentPK=Child1.ParentFK join Child Child2 on ParentPK=Child2.ParentFK join Child Child3 on ParentPK=Child3.ParentFK join Child Child4 on ParentPK=Child4.ParentFK Where Child1.field = 'a' and Child2.field='b' and Child3.field='c' and Child4.field='d'
While awkward to look at, it should be using only key fields and optimized even by Oracle.
On Thu, Jun 22, 2023 at 7:23 AM Garrett Fitzgerald sarekofvulcan@gmail.com wrote:
Is there an idiomatic way to get parent records that have all of the child records a, b, c, and d, short of joining the child table 4 times? Oracle 19, if it's relevant.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]