Search code examples
spring-bootoracle12cag-grid-angular

Filters on Correlated Subquery returns null oracle

I have the below query which combines the child table records as comma separated strings. But when i try applying filters to the correlated subquery, values be replaced as null instead of removing the record.

Parent Table : Employee

Id Name
I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim
I1BF91860BCB711EDBF4EF0FBCBCA9E18 Sam
I1BF91860BCB711EDBF4EF0FBCBCA9E19 Rohan

Child Table: Trainings

Id Name Employee Id
I1BF91860BCB711EDBF4EF0FBCBCA9P10 Java Basics I1BF91860BCB711EDBF4EF0FBCBCA9E17
I1BF91860BCB711EDBF4EF0FBCBCA9P11 Angular I1BF91860BCB711EDBF4EF0FBCBCA9E17
I1BF91860BCB711EDBF4EF0FBCBCA9P12 DotNet I1BF91860BCB711EDBF4EF0FBCBCA9E18

Below is the query which results in the output as shown below

select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id") 
  FROM trainings tr WHERE e."Id" = tr."Employee Id")
  trainingss
from employee e

output:

enter image description here

As shown in the image, child values are grouped as comma separated string as expected, but i am having issues while filtering the child values.

How to apply filters on child column values with the subqueries used. Say i wanted to display rows with JavaBasics and Angular, and expected should be first row. But in the below output image i could see all the rows being displayed with Null values.

What i tried?

select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id") 
  FROM trainings tr WHERE e."Id" = tr."Employee Id" and tr."Id" = 'I1BF91860BCB711EDBF4EF0FBCBCA9P10')
  trainingss
from employee e

Output:

enter image description here

Please note, above example is just a sample of what our requirement is and in our real case scenarios we have more than 150 columns and all are dynamic. Hence we don't want to use GroupBy

Solution

  • You can use exists for every condition or count the empoyees that have the number of trainings if they are exactly like the number training you search

    select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id") 
  FROM trainings tr WHERE e."Id" = tr."Employee Id"
  )
  trainings
from employee e 
WHERE EXISTS (SELECT 1 FROM trainings tr1 WHERE e."Id" = tr1."Employee Id"
  AND tr1."Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P10'))
    ID NAME TRAININGS
    I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics,Angular 
    select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id") 
  FROM trainings tr WHERE e."Id" = tr."Employee Id"
  )
  trainings
from employee e 
WHERE EXISTS (SELECT 1 FROM trainings tr1 WHERE e."Id" = tr1."Employee Id"
  AND tr1."Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P11'))
AND EXISTS (SELECT 1 FROM trainings tr1 WHERE e."Id" = tr1."Employee Id"
  AND tr1."Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P10'))
    ID NAME TRAININGS
    I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics,Angular 
    select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id") 
  FROM trainings tr WHERE e."Id" = tr."Employee Id"
  )
  trainings
from employee e 
WHERE e."Id" IN
  (SELECT "Employee Id" FROM trainings 
  WHERE "Id" IN ('I1BF91860BCB711EDBF4EF0FBCBCA9P10','I1BF91860BCB711EDBF4EF0FBCBCA9P11')
  GROUP BY "Employee Id"
HAVING COUNT(*) = 2)
    ID NAME TRAININGS
    I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics,Angular

    fiddle