Pessoal sou iniciante em mysql e to meio perdido, preciso fazer uma consulta entre duas tabelas (Perfil e Questoes) vou tentar explicar o problema:
Sobre o Questionario:
Dado de parametros a Disciplina, tenho o perfil de questões para o questionário( DISCIPLINA, ANO e TIPO e suas respectivas quantidades de questões normais e questões extra), a SQL então deve retornar as questões que atendam as exigencias de QUANTIDADE (NORMAL E EXTRA) CONFORME Os TIPOS cadastrados em PERFIL.
Informação extra: as questões devem ser unicas, podem ser sorteadas de forma aleatória desde que não se repitam.
Minha tentativa foi com esse codigo (sem sucesso):
Segue o Código:
SELECT
COALESCE(CatParent.id, Questoes.id) id,
COALESCE(CatParent.enunciado, Questoes.enunciado) enunciado,
perfil.tipo_id
FROM perfil
JOIN Questoes ON perfil.tipo_id = Questoes.tipo_id and
Questoes.ano_id = perfil.ano_id
LEFT JOIN Questoes AS CatParent ON Questoes.tipo_id = CatParent.id
ORDER BY RAND( )
group by id, enunciado, tipo_id
Porém ele não limita a quantidade de questões por tipo, não sei se seria possivel usar a clausula LIMIT definindo dinamicamente tais quantidades, ou se há outra forma de separar a consulta em duas partes uma trazendo a quantidade de questões normais por tipo, outra as questões extra.
Deixa eu entender, você tem uma relação de 1:N de disciplina para perfil (cada disciplina possui N perfis), isso?
E o que você quer, como resultado da consulta, exatamente?
Vamos por partes.
Você mistura tudo, não coloca as coisas de forma ordenada, provavelmente estejas se confundindo por conta disso.
O que eu entendi, até aqui, é que você tem:
Disciplina 1 : N Perfil
Certo?
Daí, do nada, você fala de uma outra tabela (veja, no texto inicial você começa dizendo que tem duas tabelas e não 3), onde, a partir de resultados da segunda (perfis) você quer trazer resultados da tabela questões.
Uma coisa que eu não sei, ainda, é se estas tabelas já estão geradas, fixas e não podem ser alteradas ou se é você que está criando estas tabelas, para o sistema que está desenvolvendo, podendo, portanto, alterá-las conforme necessário.
Se você puder alterar, facilita muito. Senão, teremos que pensar em como seria a melhor abordagem.
O primeiro passo é entender a relação entre as três tabelas envolvidas nessa questão: disciplina, perfil e questões.
Este modelo que você colocou das tabelas não contempla a multiplicidade entre tipo e perfil. Você tem uma FK de tipo em perfil, o que me leva a crer que a relação é de 1 : N (cada tipo possui diferentes perfis). Isso vai contra, até onde entendi, o que você quer.
A relação que eu entendo que deveria haver entre tipo e perfil é de N : M (muitos perfis para muitos tipos), aí sim ficaria viável buscar o que você está tentando trazer.
mesmo com a relação Perfil.TIPO_ID (FK) e Tipo.TIPO_ID ? pensei que nela cada tipo pudesse estar associado a varios perfis, e por eles eu conseguiria estabelecer essa ligação, não da por esses meios então? outra duvida: relação N:M geraria outra tabela?
Como cada tipo possui vários perfis, você consegue, a partir de perfil, identificar um único tipo. Como tipo também se relaciona com muitas questões, você consegue, para cada perfil, obter várias questões.
Segundo ponto, sim, toda relação N : M gera uma tabela associativa.
Usei duas procedures ( uma que obtem o perfil outra que obtem as questoes:
A primeira recupera o PERFIL, percorre cada registro e invoca a de questões;
CREATE PROCEDURE questionario(IN params)
BEGIN
#VARIAVEIS AUXILIARES....
...
DECLARE tbPerfil CURSOR FOR
SELECT TOTAL_QUE, TOTAL_QUE_EXTRA, TIPO_ID from perfilas d where disc_id = disc;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
CREATE PROCEDURE questionario(IN disc_id )
BEGIN
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
#Criação da tabela temporaria
CREATE TEMPORARY TABLE tmp_questionario (
[Fields...]
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
get_dinamic: LOOP
FETCH tbPerfil INTO [vars];
#encerra o loop
IF v_finished = 1 THEN
LEAVE get_Perfil;
END IF;
if(limq > 0) then
#inclui as questoes normais
CALL cria_questionario(tpo_id,graduate,limq, 'NORMAL' );
END IF;#limitq>0
#questões NORMAIS
if(limqe>0) then
CALL cria_questionario(tpo_id,graduate,limqe, 'EXTRA' );
#inclui as questoes na lista
END IF;#limitq>0
END LOOP get_perfil;
#retorna os dados
CLOSE tbPerfil;
SELECT * FROM tmp_questionario;
END;
Para buscar as questões:
CREATE PROCEDURE cria_questionario (IN params )
BEGIN
#Declaração das variaveis
DECLARE done BOOLEAN DEFAULT FALSE;
#Sql que obtem as questões pelo tipo
DECLARE cursor_a CURSOR FOR
select distinct QUESTAO_ID,
ENUNCIADO ,
...
from Questoes q ,tipo t, serie s
where q.TIPO_id = :tipo_id and
q.ANO_ID = :ANO_ID and
q.ANO_ID = s.ANO_ID and
t.TIPO_ID = q.TIPO_id
order by RAND() limit limite;
OPEN cursor_a;
REPEAT FETCH cursor_a INTO [var_values_declaradas];
IF NOT done THEN
INSERT INTO tmp_questionario ([fields]) VALUE([var_values_declaradas]);
END IF;
UNTIL done END REPEAT;
CLOSE cursor_a;
Não é a melhor solução, mas foi a que consegui, se houverem sugestões serão bem vindas.
