Join table using a SubQuery

Question

Join table using a SubQuery

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

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

PROOF

IDPROVA | DESCPROVA | TYPE | QTDEMAX

REGISTRATION

PROFESSOR | SCHOOL (IDESCOLA) | DTINSCRICAO | IDPROVA

INSCRICAOALUNO

ALUNO (IDALUNO) | SCHOOL | DTINSCRICAO | IDPROVA

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

I'm trying to do the following:

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 it appears:

"right parenthesis not found"

I would like to know 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 is any I would like some light on how I can do this ..

join oracle

asked by anonymous 24.03.2015 / 17:53

2 answers

Match rows of two DBGrids What is the difference between the following methods?

score 1 · Answer 1

Well, I do not know if it's really the best solution, but I've managed to solve it ...

Instead of trying to create this crazy logic of creating A 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;

score 1 · Answer 2

About the error message:

"right parenthesis not found"

The problem is that the alias of the temporary table created has been called.

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 intended structure:

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

It's definitely a bad idea. Imagine the over-processing that Oracle will have to do to accomplish this join .

You will need to bring all records from the Prova table, without filtering;

For each record, perform the Join operation and create a new temporary table;

Perform a Union on all temporary created to create a single temporary table to perform Left Join ;

Run Left Join of all Prova against a temporary one, with no index.

Run filter on Where finally filtering what you need;

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

1 (Proof) + (Records Qty in Proof) + 1 (Temp after Union)

That is, totally insane. Not counting processing costs to make count() and group by . And another, the last clause WHERE kills the LEFT JOIN s. Of a revised.

A simpler and more direct proposal follows. I can not run here with me, but if it does not work, make small adjustments and let me know that I update the answer.

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 .