(Resolvido) SQL - problema ao relacionar atríbutos de duas tabelas com claúsula IN

[mnmn]

Olá amigos, poderiam me ajudar?

Tenho no meu banco de dados uma tabela salao que tem N registros na tabela funcionários.

SELECT s.nome_salao, f.nome_funcionario FROM funcionario f NATURAL JOIN salao s WHERE s.email_salao IN (SELECT email_salao FROM salao WHERE codigo_estado = 2 AND cidade_salao = 'São Paulo' AND bairro_salao = 'Limoeiros') ORDER BY f.nome_funcionario;

Tenho uma query em que desejo selecionar o nome do salão ao qual o funcionário pertence. Porém quero fazer essa pesquisa para todos os salões que estejam em determinada localidade.

A subquery retorna dois salões, e para cada salão tenho vários funcionários.

Então quero retornar somente o nome do salão ao qual cada um dos funcionários pertence.

[Denis Courcy]

Faça assim:

1. Crie índice por email_salao na tabela salao.
2. Crie índice por (codigo_estado,cidade_salao,bairro_salao) na tabela salao
3. Crie índice por email_salao na tabela funcionario
4. Crie índice por nome_funcionario na tabela funcionario
5. Mude sua query para:

   SELECT s.nome_salao, f.nome_funcionario 
   FROM funcionario f 
   INNER JOIN salao s ON s.email_salao = f.email_salao
   WHERE  s.codigo_estado = 2 AND s.cidade_salao = 'São Paulo' AND s.bairro_salao = 'Limoeiros'
   ORDER BY f.nome_funcionario;

 

[mnmn]

Faça assim:

1. Crie índice por email_salao na tabela salao.
2. Crie índice por (codigo_estado,cidade_salao,bairro_salao) na tabela salao
3. Crie índice por email_salao na tabela funcionario
4. Crie índice por nome_funcionario na tabela funcionario
5. Mude sua query para:

   SELECT s.nome_salao, f.nome_funcionario 
   FROM funcionario f 
   INNER JOIN salao s ON s.email_salao = f.email_salao
   WHERE  s.codigo_estado = 2 AND s.cidade_salao = 'São Paulo' AND s.bairro_salao = 'Limoeiros'
   ORDER BY f.nome_funcionario;

 

Denis Courcy, muito obrigado por me ajudar. Passei horas tentando fazer com que isso funcionasse e deu tudo certinho da maneira que você sugeriu! Muito obrigado.

[mnmn]

Denis, tenho um outro probleminha, poderia me ajudar?

Preciso selecionar todos horários dos funcionários (horario_horariofuncionario) de acordo com a filtragem de serviço, data e dia da semana. 

Estou conseguindo fazer esse select normalmente, porém como são vários horários (horario_horariofuncionario) está sendo gerado um novo resultado para cada novo horário encontrado... E nesses resultados, claro, o único atríbuto que os diferencia é o horario_horariofuncionario o resto dos atríbutos são todos iguais.

Então o que eu gostaria é que todos os horario_horariofuncionario fossem retornados em APENAS UM RESULTADO juntamente com o restante dos atríbutos em vez de retornar um novo resultado para cada horario_horariofuncionario...

SELECT hf.horario_horariofuncionario,
hf.codigo_horariofuncionario,
s.nome_servico,
ss.tempo_servicosalao,
ss.preco_servicosalao,
hf.data_horariofuncionario,
ds.nome_diasemana,
f.nome_funcionario,
f.apelido_funcionario,
f.foto_funcionario FROM servicosalao ss
INNER JOIN servico s
INNER JOIN diasemana ds
INNER JOIN horariofuncionario hf
INNER JOIN funcionario f
INNER JOIN servicofuncionario sf
ON hf.codigo_funcionario = f.codigo_funcionario 
WHERE sf.codigo_servicosalao = :servico 
AND hf.data_horariofuncionario = :data 
AND ds.nome_diasemana = :diaDaSemana 
AND hf.codigo_statushorariofuncionario = 1;

 

Editado Setembro 1, 2015 por mnmn

[Denis Courcy]

Eu analisei sua query e não consegui  entender o relacionamento das tabelas que ali estão. Poderia, por favor, publicar a estrutura delas para que eu possa enviar uma resposta correta?

[mnmn]

Claro Denis Courcy, seguem as estruturas:

Tabela servico:

CREATE TABLE  `salao`.`servico` (
  `codigo_servico` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome_servico` varchar(100) NOT NULL,
  PRIMARY KEY (`codigo_servico`)
) ENGINE=InnoDB AUTO_INCREMENT=462 DEFAULT CHARSET=latin1;

 

Tabela diasemana:

CREATE TABLE  `salao`.`diasemana` (
  `codigo_diasemana` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome_diasemana` varchar(15) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`codigo_diasemana`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

 

Tabela horariofuncionario:

CREATE TABLE  `salao`.`horariofuncionario` (
  `codigo_horariofuncionario` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `codigo_funcionario` int(10) unsigned NOT NULL,
  `horario_horariofuncionario` varchar(5) CHARACTER SET latin1 NOT NULL,
  `codigo_diasemana` int(10) unsigned NOT NULL,
  `data_horariofuncionario` varchar(10) COLLATE utf8_general_mysql500_ci NOT NULL,
  `codigo_statushorariofuncionario` int(10) unsigned NOT NULL,
  PRIMARY KEY (`codigo_horariofuncionario`),
  KEY `FK_horariofuncionario_1` (`codigo_funcionario`),
  KEY `FK_horariofuncionario_2` (`codigo_diasemana`),
  KEY `FK_horariofuncionario_3` (`codigo_statushorariofuncionario`),
  CONSTRAINT `FK_horariofuncionario_1` FOREIGN KEY (`codigo_funcionario`) REFERENCES `funcionario` (`codigo_funcionario`),
  CONSTRAINT `FK_horariofuncionario_2` FOREIGN KEY (`codigo_diasemana`) REFERENCES `diasemana` (`codigo_diasemana`),
  CONSTRAINT `FK_horariofuncionario_3` FOREIGN KEY (`codigo_statushorariofuncionario`) REFERENCES `statushorariofuncionario` (`codigo_statushorariofuncionario`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

 

Tabela funcionario:

CREATE TABLE  `salao`.`funcionario` (
  `codigo_funcionario` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cidade_funcionario` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `bairro_funcionario` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `logradouro_funcionario` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `cep_funcionario` varchar(9) CHARACTER SET latin1 DEFAULT NULL,
  `complemento_funcionario` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `email_funcionario` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `telefone1_funcionario` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `telefone2_funcionario` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `telefone3_funcionario` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `codigo_estado` int(10) unsigned NOT NULL,
  `foto_funcionario` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `email_salao` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `nome_funcionario` varchar(100) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `apelido_funcionario` varchar(30) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  PRIMARY KEY (`codigo_funcionario`),
  KEY `FK_funcionario_1` (`codigo_estado`),
  KEY `FK_funcionario_2` (`email_salao`),
  CONSTRAINT `FK_funcionario_1` FOREIGN KEY (`codigo_estado`) REFERENCES `estado` (`codigo_estado`),
  CONSTRAINT `FK_funcionario_2` FOREIGN KEY (`email_salao`) REFERENCES `salao` (`email_salao`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

 

Tabela servicofuncionario:

CREATE TABLE  `salao`.`servicofuncionario` (
  `codigo_servicofuncionario` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `codigo_funcionario` int(10) unsigned NOT NULL,
  `codigo_servicosalao` int(10) unsigned NOT NULL,
  PRIMARY KEY (`codigo_servicofuncionario`),
  KEY `FK_servicofuncionario_1` (`codigo_funcionario`),
  KEY `FK_servicofuncionario_2` (`codigo_servicosalao`),
  CONSTRAINT `FK_servicofuncionario_1` FOREIGN KEY (`codigo_funcionario`) REFERENCES `funcionario` (`codigo_funcionario`),
  CONSTRAINT `FK_servicofuncionario_2` FOREIGN KEY (`codigo_servicosalao`) REFERENCES `servicosalao` (`codigo_servicosalao`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

 

Preciso fazer como na imagem abaixo... preciso selecionar os atríbutos abaixo

s.nome_servico,
ss.tempo_servicosalao,
ss.preco_servicosalao,
hf.data_horariofuncionario,
ds.nome_diasemana,
f.nome_funcionario,
f.apelido_funcionario,
f.foto_funcionario

e os N horários (horariofuncionario) que o funcionário tem... tudo junto.

 

Editado Setembro 2, 2015 por mnmn

[Denis Courcy]

Tente assim:

SELECT hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao, 
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario, f.apelido_funcionario,
   f.foto_funcionario, GROUP_CONCAT(hf.horario_horariofuncionario) AS horario 
FROM servicosalao ss
INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico
INNER JOIN servicofuncionario sf sf.codigo_servicosalao = ss.codigo_servicosalao
INNER JOIN funcionario f f.codigo_funcionario = f.codigo_funcionario = sf.codigo_funcionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario
INNER JOIN diasemana ds ON ds.codigo_diasemana = hf.codigo_diasemana
WHERE sf.codigo_servicosalao = :servico AND hf.data_horariofuncionario = :data 
   AND ds.nome_diasemana = :diaDaSemana AND hf.codigo_statushorariofuncionario = 1
GROUP BY hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao, 
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario;

Informe o que foi retornado

Editado Setembro 2, 2015 por Denis Courcy

[mnmn]

Denis Courcy, fiz algumas interferências no código pois não estava retornando resultados.

Ficou assim:

SELECT hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao,
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario, f.apelido_funcionario,
   f.foto_funcionario, GROUP_CONCAT(hf.horario_horariofuncionario) AS horario 
FROM servicosalao ss
INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico
INNER JOIN servicofuncionario sf ON sf.codigo_servicosalao = ss.codigo_servicosalao
INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario
INNER JOIN diasemana ds ON ds.codigo_diasemana = hf.codigo_diasemana
WHERE sf.codigo_servicosalao = 6 AND hf.data_horariofuncionario = '28/08/2015'
   AND ds.nome_diasemana = 'Sexta-feira' AND hf.codigo_statushorariofuncionario = 1
GROUP BY hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao, 
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario;

 

Apenas coloquei algumas claúsulas ON que não tinham e mudei esta linha:

INNER JOIN funcionario f f.codigo_funcionario = f.codigo_funcionario = sf.codigo_funcionario

porque haviam duas vezes a referência para f.codigo_funcionario

 

Mas o resultado foram duas linhas... porque existem dois horários (horario_horariofuncionario) diferentes... vi que você usou o GROUP_CONCAT mas ainda assim estão sendo geradas uma linha para cada novo horário (horario_horariofuncionario).

[Denis Courcy]

Bom dia.

Apenas coloquei algumas claúsulas ON que não tinham e mudei esta linha:

INNER JOIN funcionario f f.codigo_funcionario = sf.codigo_funcionario

porque haviam duas vezes a referência para f.codigo_funcionario

A modificação acima não existe. 

O correto é:

INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario

Que é a ligação entre a tabela funcionário e a tabela servicofuncionario.

explicando as ligações entre as tabelas

INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico -- Liga a tabela servico com a tabela servicosalao
INNER JOIN servicofuncionario sf ON sf.codigo_servicosalao = ss.codigo_servicosalao -- Liga a tabela servicofuncionario com a tabela servicosalao
INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario -- liga a tabela funcionario com a tabela servicofuncionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario -- liga a tabela horariofuncionario com a tabela funcionario

[mnmn]

Bom dia Denis Courcy.

Então a cláusula final ficou assim:

SELECT hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao,
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario, f.apelido_funcionario,
   f.foto_funcionario, GROUP_CONCAT(hf.horario_horariofuncionario) AS horario 
FROM servicosalao ss
INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico
INNER JOIN servicofuncionario sf ON sf.codigo_servicosalao = ss.codigo_servicosalao
INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario
INNER JOIN diasemana ds ON ds.codigo_diasemana = hf.codigo_diasemana
WHERE sf.codigo_servicosalao = 6 AND hf.data_horariofuncionario = '28/08/2015'
   AND ds.nome_diasemana = 'Sexta-feira' AND hf.codigo_statushorariofuncionario = 1
GROUP BY hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao, 
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario;

Porém ainda estão sendo geradas duas linhas... uma para cada horario_horariofuncionario.

O GROUP_CONCAT não funcionou neste caso.

[Denis Courcy]

Poderia, por favor publicar o resultado desta query abaixo:

SELECT hf.codigo_horariofuncionario, s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao,
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario, f.apelido_funcionario,
   f.foto_funcionario, hf.horario_horariofuncionario
FROM servicosalao ss
INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico
INNER JOIN servicofuncionario sf ON sf.codigo_servicosalao = ss.codigo_servicosalao
INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario
INNER JOIN diasemana ds ON ds.codigo_diasemana = hf.codigo_diasemana
WHERE sf.codigo_servicosalao = 6 AND hf.data_horariofuncionario = '28/08/2015'
   AND ds.nome_diasemana = 'Sexta-feira' AND hf.codigo_statushorariofuncionario = 1;

Quero ver se há alguma duplicidade que esteja impedindo o group_concat

[mnmn]

Denis Courcy, o resultado é este:

22, 'Absolut control - hidratação L'Óreal', 30, '40,00', '28/08/2015', 'Sexta-feira', 'Ziraldo de Almeida Lopes', 'Guga', '', '13:00'
23, 'Absolut control - hidratação L'Óreal', 30, '40,00', '28/08/2015', 'Sexta-feira', 'Ziraldo de Almeida Lopes', 'Guga', '', '18:00'

 

[Denis Courcy]

OK. Tente assim:

SELECT s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao,
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario, f.apelido_funcionario,
   f.foto_funcionario, GROUP_CONCAT(hf.horario_horariofuncionario) AS horario, 
   GROUP_CONCAT(hf.codigo_horariofuncionario) as Codigos_Horarios
FROM servicosalao ss
INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico
INNER JOIN servicofuncionario sf ON sf.codigo_servicosalao = ss.codigo_servicosalao
INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario
INNER JOIN diasemana ds ON ds.codigo_diasemana = hf.codigo_diasemana
WHERE sf.codigo_servicosalao = 6 AND hf.data_horariofuncionario = '28/08/2015'
   AND ds.nome_diasemana = 'Sexta-feira' AND hf.codigo_statushorariofuncionario = 1
GROUP BY s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao, 
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario;

[mnmn]

Muito bom Denis Courcy, ficou assim:

'Absolut control - hidratação L'Óreal', 30, '40,00', '28/08/2015', 'Sexta-feira', 'Ziraldo de Almeida Lopes', 'Guga', '', '13:00,18:00', '22,23'

Porém preciso do codigo_horariofuncionario junto do horario_horariofuncionario porque cada registro de horario_horariofuncionario será um botão e quando clicar no botão preciso pegar o codigo_horariofuncionario relativo ao horario_horariofuncionario clicado.

Tentei fazendo o GROUP_CONCAT assim: GROUP_CONCAT(codigo_horariofuncionario, horario_horariofuncionario) mas ainda foram geradas duas linhas de resultado.

O resultado final precisa ser como neste foto:

[Denis Courcy]

Mude para que fique assim:

SELECT s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao,
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario, f.apelido_funcionario,
   f.foto_funcionario, GROUP_CONCAT(CONCAT(hf.codigo_horariofuncionario,'->',hf.horario_horariofuncionario)) AS Codigos_e_Horarios
FROM servicosalao ss
INNER JOIN servico s ON s.codigo_servico = ss.codigo_servico
INNER JOIN servicofuncionario sf ON sf.codigo_servicosalao = ss.codigo_servicosalao
INNER JOIN funcionario f ON f.codigo_funcionario = sf.codigo_funcionario
INNER JOIN horariofuncionario hf ON hf.codigo_funcionario = f.codigo_funcionario
INNER JOIN diasemana ds ON ds.codigo_diasemana = hf.codigo_diasemana
WHERE sf.codigo_servicosalao = 6 AND hf.data_horariofuncionario = '28/08/2015'
   AND ds.nome_diasemana = 'Sexta-feira' AND hf.codigo_statushorariofuncionario = 1
GROUP BY s.nome_servico, ss.tempo_servicosalao, ss.preco_servicosalao, 
   hf.data_horariofuncionario, ds.nome_diasemana, f.nome_funcionario;

[mnmn]

Denis Courcy, perfeito!!!

Você é DBA?

Muito obrigado mesmo.

[Denis Courcy]

DBA, Não. Entre outras funções sou AD.

Editado Setembro 4, 2015 por Denis Courcy