inner join table on 1 = 1 can be considered a gambiarra?

Question

inner join table on 1 = 1 can be considered a gambiarra?

Navigation

#1 by (4 votes)

1

I have a table of provas and a table of alunos that have no direct relationship, I still have a inscrição aluno table and a escolas table, as follows in the modeling below:

Ineedtocreateaselecttobringhowmanystudentsfromeachschoolareenrolledineachtest.

IranintoagambiarrawithgreatcaveatsbecauseSELECTworkedperfectly:

SELECT(SELECTcount(ia.idaluno)FROMinscricaoalunoiaINNERJOINalunosaONa.idaluno=ia.idalunoWHEREa.idpessoa_juridica=e.idpessoa_juridicaANDia.idprova=p.idprova)qtd,e.nome,e.idpessoa_juridica,p.descprova,p.idprovaFROMescolaseINNERJOINprovapON1=1GROUPBYe.nome,e.idpessoa_juridica,p.descprova,p.idprovaORDERBYe.nome;

"I created a nonexistent relationship" from the school table with the test table using a INNER JOIN ON 1 = 1 , so I listed all the tests for each school if I have the following data for example:

This"nonexistent" relationship will bring me the following result:

With this I create a subquery in the data that brings me the amount of each student in each test according to the school he studies.

Now that doubt comes, is this a gambiarra? The result is satisfactory, it works, but many people do not welcome the use of the 1 = 1 artifact

sql oracle select

asked by anonymous 02.06.2015 / 15:26

1 answer

Bootstrap news slider FOREACH or FOR? [duplicate]

score 4 · Accepted Answer

Yes, it is a gambiarra. The correct one in this case would be to apply a CROSS JOIN

SELECT (SELECT count(ia.idaluno) FROM inscricaoaluno ia INNER JOIN alunos a ON a.idaluno = ia.idaluno
WHERE a.idpessoa_juridica = e.idpessoa_juridica AND ia.idprova = p.idprova) qtd, e.nome, e.idpessoa_juridica , p.descprova, p.idprova  
FROM escolas e
CROSS JOIN prova p
GROUP BY e.nome, e.idpessoa_juridica , p.descprova, p.idprova  
ORDER BY e.nome;

An alternate syntax can be:

SELECT (SELECT count(ia.idaluno) FROM inscricaoaluno ia INNER JOIN alunos a ON a.idaluno = ia.idaluno
WHERE a.idpessoa_juridica = e.idpessoa_juridica AND ia.idprova = p.idprova) qtd, e.nome, e.idpessoa_juridica , p.descprova, p.idprova  
FROM escolas e, prova p
GROUP BY e.nome, e.idpessoa_juridica , p.descprova, p.idprova  
ORDER BY e.nome;