Merge table using a Subquery

Question

I have 3 tables: inscricaoaluno, inscricaoescola and prova, with the following structures:

PROOF

IDPROVA | DESCPROVA | TYPE | QTDEMAX

ENROLLED

TEACHER | SCHOOL(IDESCOLA) | DTINSCRICAO | IDPROVA

INSCRIBED

STUDENT(IDALUNO) | SCHOOL | DTINSCRICAO | IDPROVA

According to the tipo of the test, 'COLLECTIVE' or 'INDIVIDUAL' I will get all enrolled schools and/or all enrolled students, for this I would like to make a query to get only the quantities...

Here’s what I’m trying to do:

select * from prova p 
left join (
    case when p.tipo='COLETIVO' then 
        (SELECT ie.idProva, count(ie.idEscola) as qtde FROM inscricaoescola ie
        group by ie.idProva) 
    else 
        (SELECT ia.idProva, count(ia.idaluno) as qtde FROM inscricaoaluno ia
        group by ia.idProva)
    end as cont) 
on cont.idprova = p.idprova;

In the Sqldeveloper console appears:

"parenthesis right not found"

I wonder if it is possible to create a Query with this structure that I tried to do /\

left join (case {condição} then {subquery} else {outra_subquery} )

If there aren’t any I’d like some light on how I can do that..

Answer 1

About the error message:

"parenthesis right not found"

The problem is left speaking the alias of the created temporary table.

select * from prova p 
left join (
    case when p.tipo='COLETIVO' then 
        (SELECT ie.idProva, count(ie.idEscola) as qtde FROM inscricaoescola ie
        group by ie.idProva) 
    else 
        (SELECT ia.idProva, count(ia.idaluno) as qtde FROM inscricaoaluno ia
        group by ia.idProva)
    end)  as cont '<<<<<<<<<<< 
on cont.idprova = p.idprova;

About your intention with the sought structure:

left join (case {condição} then {subquery} else {outra_subquery} )

It’s definitely a bad idea. Imagine the super processing that Oracle will have to do to accomplish this join.

1. You will have to bring all the records from the table Prova, unfiltered;
2. For each record, perform the operation of the Join and create a new temporary table;
3. Perform a Union in all temps created to create a single temporary table to perform the Left Join;
4. Execute the Left Join of all the records of Prova against a temporary, without any index.
5. Run the filter on Where finally filtering out what you need;

In the end, numerous tables will be involved, to calculate:

1 (Proof) + (Qtd of Records in Proof) + 1 (Temp. after Union)

That is, totally insane. Not counting the processing costs to realize the count() and group by. And another, the last clause WHERE bush the LEFT JOINs. From a revised.

It follows a simpler and more direct proposal. I have no way to run here with me, but if it doesn’t work, make small adjustments and let me know I’ll update the response.

SELECT P.*,
       COUNT(IE.IdEscola) OVER(PARTITION BY IE.IdProva) QtdeIE,
       COUNT(IA.IdAluno) OVER(PARTITION BY IA.IdAluno) QtdeIA
  FROM Prova p
  JOIN InscricaoEscola IE
    ON P.Tipo = 'COLETIVO' AND IE.IdProva = P.IdProva
  JOIN InscricaoAluno IA
    ON P.Tipo = 'INDIVIDUAL' AND IA.IdProva = P.IdProva

See more about COUNT OVER PARTITION BY.

Answer 2

Well, I don’t know if it’s really the great solution, but I managed to solve...

Instead of trying to create this crazy logic of creating ONE left join with a case inside, I created two left's join:

select * from prova p 
left join (SELECT ie.idProva, count(ie.idEscola) as qtde FROM inscricaoescola ie
        group by ie.idProva) contE on cont.idprova = p.idprova
left join
        (SELECT ia.idProva, count(ia.idaluno) as qtde FROM inscricaoaluno ia
        group by ia.idProva) contA on cont.idprova = p.idprova;