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 ---
Have you considered using UNION? Still four queries, but at least not four joins.
Paul H. Tarver Tarver Program Consultants, Inc.
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Garrett Fitzgerald Sent: Thursday, June 22, 2023 6:23 AM To: profoxtech@leafe.com Subject: [NF] SQL ALL, but not quite
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]
Garrett,
Something like this?
http://sqlfiddle.com/#!4/5ba4f0/2
On Thu, Jun 22, 2023 at 12:23 PM 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]
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]
Yeah, that was the q&d way I ended up implementing it - with 7 child tables. :) I just couldn't believe nobody had added an idiomatic way to do it in the language without jumping through hoops to get them. It's an immunization record - I wanted all the students that had shots for Measles 1, Measles 2, Mumps 1, Mumps 2, Rubella 1, Rubella 2, and NOT MMR1. Surprisingly enough, we actually have 12 active students who meet that criteria. :)
On Thu, Jun 22, 2023 at 3:41 PM Ted Roche tedroche@gmail.com wrote:
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]
As usual, the devil's in the details. With more details, there might have been more elegant solutions. Oracle has options like Cross-tabulation and Pivot that might give you the results you want, especially if this turns out to be an ongoing request with new vaccines coming around.
On Thu, Jun 22, 2023 at 3:50 PM Garrett Fitzgerald sarekofvulcan@gmail.com wrote:
Yeah, that was the q&d way I ended up implementing it - with 7 child tables. :) I just couldn't believe nobody had added an idiomatic way to do it in the language without jumping through hoops to get them. It's an immunization record - I wanted all the students that had shots for Measles 1, Measles 2, Mumps 1, Mumps 2, Rubella 1, Rubella 2, and NOT MMR1. Surprisingly enough, we actually have 12 active students who meet that criteria. :)
On Thu, Jun 22, 2023 at 3:41 PM Ted Roche tedroche@gmail.com wrote:
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]
Most probably not. We were looking for test students for a new integration. While our student records system has dev, test, and prod, the immunization tracker is live, so we can only mess with it Very Carefully. :)
I'll read up on those other two options just in case, though. I know what they look like, but not how to code them.
On Thu, Jun 22, 2023, 16:35 Ted Roche tedroche@gmail.com wrote:
As usual, the devil's in the details. With more details, there might have been more elegant solutions. Oracle has options like Cross-tabulation and Pivot that might give you the results you want, especially if this turns out to be an ongoing request with new vaccines coming around.
On Thu, Jun 22, 2023 at 3:50 PM Garrett Fitzgerald sarekofvulcan@gmail.com wrote:
Yeah, that was the q&d way I ended up implementing it - with 7 child tables. :) I just couldn't believe nobody had added an idiomatic way to
do
it in the language without jumping through hoops to get them. It's an immunization record - I wanted all the students that had shots for
Measles
1, Measles 2, Mumps 1, Mumps 2, Rubella 1, Rubella 2, and NOT MMR1. Surprisingly enough, we actually have 12 active students who meet that criteria. :)
On Thu, Jun 22, 2023 at 3:41 PM Ted Roche tedroche@gmail.com wrote:
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]
-
António Tavares Lopes -
Garrett Fitzgerald -
Paul H. Tarver -
Ted Roche