Jump to content
Fórum Script Brasil
  • 0

Dúvida no COUNT(val) com JOIN

Zek

Asked by Zek,

 Share

Question

Zek

Zek

Posted
Posted

Bom dia a todos, estou desenvolvendo um sistema, e travei em uma consulta, gostaria de saber se alguém pode me dar uma dica pra eu resolver esta pendência.

É o seguinte: tenho 4 tabelas e preciso retornar algumas combinações de dados entre elas, tenho 2 consultas:

Esta traz corretamente o count que informa a quantidade de recomendações, mas repete os anúncios:

SELECT DISTINCT A.`codAnuncio`,A.`tituloAnuncio`, A.`imgAnuncio`, COUNT(B.`FK_Anuncio_RA`) AS `qtdRecom`, D.`nmCatPadrao` 
FROM `tbanuncio` AS A 
LEFT JOIN `tbrecomendanuncio` AS B ON A.`codAnuncio` = B.`FK_Anuncio_RA` 
INNER JOIN `tbcategoriaanuncio` AS C ON A.`codAnuncio` = C.`FK_Anuncio_CA` 
INNER JOIN `tbcategoriapadrao` AS D ON C.`FK_CatPd_CA` = D.`codCatPadrao` 
WHERE A.`statusAnuncio` = 2 AND A.`statusFree` = 2 AND A.`codEntrega` = 1 
GROUP BY A.`FK_CatGeral_A`,A.`codAnuncio`,B.`FK_Anuncio_RA`,D.`nmCatPadrao` 
ORDER BY A.`codAnuncio`ASC, COUNT(B.`FK_Anuncio_RA`) DESC
Já esta não duplica os anúncios, mas soma as quantidades de recomendações, ou seja soma o valor do COUNT(): 
SELECT DISTINCT A.`codAnuncio`,A.`tituloAnuncio`, A.`imgAnuncio`, COUNT(B.`FK_Anuncio_RA`) AS `qtdRecom`, D.`nmCatPadrao` 
FROM `tbanuncio` AS A 
LEFT JOIN `tbrecomendanuncio` AS B ON A.`codAnuncio` = B.`FK_Anuncio_RA` 
INNER JOIN `tbcategoriaanuncio` AS C ON A.`codAnuncio` = C.`FK_Anuncio_CA` 
INNER JOIN `tbcategoriapadrao` AS D ON C.`FK_CatPd_CA` = D.`codCatPadrao` 
WHERE A.`statusAnuncio` = 2 AND A.`statusFree` = 2 AND A.`codEntrega` = 1 
GROUP BY A.`FK_CatGeral_A`, B.`FK_Anuncio_RA`, B.`FK_Anuncio_RA`
ORDER BY A.`codAnuncio`ASC, COUNT(B.`FK_Anuncio_RA`) DESC
Gostaria de trazer os anúncios com suas respectivas indicações sem duplicar os anúncios ou somar as indicações, desde já agradeço a todos. Segue o código para criação das tabelas: 
CREATE TABLE IF NOT EXISTS `tbanuncio` (
  `codAnuncio` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FK_CatGeral_A` int(10) unsigned NOT NULL,
  `FK_Usuario_A` int(10) unsigned NOT NULL,
  `dtCadastro` datetime DEFAULT NULL,
  `tituloAnuncio` varchar(150) DEFAULT NULL,
  `imgAnuncio` varchar(50) DEFAULT NULL,
  `codEntrega` int(1) unsigned DEFAULT NULL,
  `planoAnuncio` int(1) unsigned NOT NULL DEFAULT '1',
  `statusAnuncio` int(1) unsigned NOT NULL DEFAULT '1',
  `statusFree` int(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`codAnuncio`),
  KEY `FK_Usuario_A` (`FK_Usuario_A`),
  KEY `FK_CatGeral_A` (`FK_CatGeral_A`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20;

--
-- Extraindo dados da tabela `tbanuncio`
--

INSERT INTO `tbanuncio` (`codAnuncio`, `FK_CatGeral_A`, `FK_Usuario_A`, `dtCadastro`, `tituloAnuncio`, `imgAnuncio`, `codEntrega`, `planoAnuncio`, `statusAnuncio`, `statusFree`) VALUES
(17, 1, 1, '2011-10-25 14:39:39', 'Casa de Carnes Melhor','19d2d922c6132bb2dcc7a52c401296df.jpg', 1, 1, 2, 2),
(18, 9, 1, '2011-10-27 14:35:51', 'Pizzaria Cantinho do Abel', '4b9b46e01a4727584a5d978aa78d4685.jpg', 1, 2, 2, 2),
(10, 9, 1, '2011-09-13 16:31:58', 'Pizzaria do Alemão', 'dcac048771a51ac370e4ae23ec4f864b.jpg', 1, 1, 2, 2),
(12, 1, 3, '2011-09-19 14:54:51', 'Lojinha da Maria', '4c4a32824848e30fa4b37307b9bab866.jpg', 1, 2, 2, 2),
(13, 3, 1, '2011-09-27 08:28:31', 'Lava Rápido do Juarez', '07c9c1411f4a063ebfbc1e33212b969b.jpg', 1, 3, 2, 2),
(14, 5, 3, '2011-09-29 15:13:27', 'Lojinha do Armando', 'f653766b4a921a4d9e71d8bf2ea903cd.jpg', 1, 3, 2, 2),
(15, 2, 2, '2011-09-30 10:23:50', 'Loja do RDunha', '8a1f818e6f410915691f2ac9285418c8.jpg', 2, 1, 2, 2),
(16, 3, 2, '2011-10-03 15:40:15', 'Centro Automotivo RDunha', '6ffe27dfacc96513a90739f6f4d596db.jpg', 1, 1, 2, 2),
(19, 1, 3, '2011-11-09 10:01:00', 'Casa de Carnes Matagato', 'dfd015b4e6cb2de370757231a82499a6.jpg', 2, 1, 2, 2);



CREATE TABLE IF NOT EXISTS `tbrecomendanuncio` (
  `codRecomend` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FK_Anuncio_RA` int(10) unsigned NOT NULL,
  `FK_Usuario_RA` int(10) unsigned NOT NULL,
  `dtCadastro` datetime DEFAULT NULL,
  PRIMARY KEY (`codRecomend`),
  KEY `FK_Anuncio_RA` (`FK_Anuncio_RA`),
  KEY `FK_Usuario_RA` (`FK_Usuario_RA`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18;

--
-- Extraindo dados da tabela `tbrecomendanuncio`
--

INSERT INTO `tbrecomendanuncio` (`codRecomend`, `FK_Anuncio_RA`, `FK_Usuario_RA`, `dtCadastro`) VALUES
(11, 12, 1, '2011-10-05 10:02:54'),
(3, 12, 3, '2011-10-03 15:13:36'),
(13, 14, 3, '2011-10-27 14:06:37'),
(5, 13, 2, '2011-10-03 15:27:06'),
(6, 13, 3, '2011-10-03 15:30:27'),
(14, 18, 1, '2011-10-27 14:45:56'),
(12, 14, 1, '2011-10-20 16:49:21'),
(10, 13, 1, '2011-10-05 09:20:19'),
(15, 14, 5, '2011-11-09 15:29:09'),
(16, 18, 5, '2011-11-09 15:51:40'),
(17, 12, 5, '2011-11-09 16:25:57');



CREATE TABLE IF NOT EXISTS `tbcategoriaanuncio` (
  `FK_CatPd_CA` int(10) unsigned NOT NULL,
  `FK_Anuncio_CA` int(10) unsigned NOT NULL,
  PRIMARY KEY (`FK_CatPd_CA`,`FK_Anuncio_CA`),
  KEY `FK_CatPd_CA` (`FK_CatPd_CA`),
  KEY `FK_Anuncio_CA` (`FK_Anuncio_CA`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Extraindo dados da tabela `tbcategoriaanuncio`
--

INSERT INTO `tbcategoriaanuncio` (`FK_CatPd_CA`, `FK_Anuncio_CA`) VALUES
(10, 12),
(14, 17),
(14, 19),
(95, 12),
(147, 12),
(152, 15),
(155, 15),
(161, 16),
(162, 16),
(171, 16),
(179, 13),
(182, 16),
(191, 14),
(194, 14),
(195, 14),
(196, 14),
(197, 14),
(199, 14),
(201, 14),
(203, 14),
(243, 10),
(246, 10),
(246, 18),
(247, 10),
(247, 18),
(252, 10),
(252, 18),
(253, 10),
(288, 17),
(288, 19);



CREATE TABLE IF NOT EXISTS `tbcategoriapadrao` (
  `codCatPadrao` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FK_CatGeral_CP` int(10) unsigned NOT NULL,
  `dtCadastro` datetime DEFAULT NULL,
  `nmCatPadrao` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`codCatPadrao`),
  KEY `FK_CatGeral_CP` (`FK_CatGeral_CP`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=289;

--
-- Extraindo dados da tabela `tbcategoriapadrao`
--

INSERT INTO `tbcategoriapadrao` (`codCatPadrao`, `FK_CatGeral_CP`, `dtCadastro`, `nmCatPadrao`) VALUES
(1, 1, '2011-11-03 13:34:42', 'Agências de Emprego'),
(2, 1, '2011-11-03 13:34:42', 'Agências de Viagem'),
(3, 1, '2011-11-03 13:34:42', 'Imobiliárias'),
(4, 1, '2011-11-03 13:34:42', 'Advocacias'),
(5, 1, '2011-11-03 13:34:42', 'Escritórios Contábeis'),
(6, 1, '2011-11-03 13:34:42', 'Financeiras'),
(7, 1, '2011-11-03 13:34:42', 'Despachantes'),
(8, 1, '2011-11-03 13:34:42', 'Corretoras'),
(9, 1, '2011-11-03 13:34:42', 'Administradoras'),
(10, 1, '2011-11-03 13:34:42', 'Bazares'),
(11, 1, '2011-11-03 13:34:42', 'Bancas de Jornais'),
(12, 1, '2011-11-03 13:34:42', 'Brechós'),
(13, 1, '2011-11-03 13:34:42', 'Copiadoras'),
(14, 1, '2011-11-03 13:34:42', 'Açougues'),
(15, 1, '2011-11-03 13:34:42', 'Chaveiros'),
(16, 1, '2011-11-03 13:34:42', 'Adegas'),
(17, 1, '2011-11-03 13:34:42', 'Carpintarias'),
(18, 1, '2011-11-03 13:34:42', 'Drogarias'),
(19, 1, '2011-11-03 13:34:42', 'Depósitos'),
(20, 1, '2011-11-03 13:34:42', 'Estúdios de Tatuagem'),
(21, 1, '2011-11-03 13:43:26', 'Floriculturas'),
(22, 1, '2011-11-03 13:43:26', 'Ateliês'),
(23, 1, '2011-11-03 13:43:26', 'Farmácias'),
(24, 1, '2011-11-03 13:43:26', 'Frigoríficos'),
(25, 1, '2011-11-03 13:43:26', 'Funerárias'),
(26, 1, '2011-11-03 13:43:26', 'Docerias'),
(27, 1, '2011-11-03 13:43:26', 'Eletrônicas'),
(28, 1, '2011-11-03 13:43:26', 'Hipermecados'),
(29, 1, '2011-11-03 13:43:26', 'Assistências Técnicas'),
(30, 1, '2011-11-03 13:43:26', 'Joalherias'),
(31, 1, '2011-11-03 13:43:26', 'Livrarias'),
(32, 1, '2011-11-03 13:43:26', 'Lan Houses'),
(33, 1, '2011-11-03 13:43:26', 'Lojas de Colchões'),
(34, 1, '2011-11-03 13:43:26', 'Lojas Esportivas'),
(35, 1, '2011-11-03 13:43:26', 'Lavanderias'),
(36, 1, '2011-11-03 13:43:26', 'Lojas de Eletrônicos'),
(37, 1, '2011-11-03 13:43:26', 'Lotéricas'),
(38, 1, '2011-11-03 13:43:26', 'Mercados'),
(39, 1, '2011-11-03 13:43:26', 'Madeireiras'),
(40, 1, '2011-11-03 13:43:26', 'Marcenarias'),
(41, 1, '2011-11-03 13:43:26', 'Óticas'),
(42, 1, '2011-11-03 13:43:26', 'Perfumarias'),
(43, 1, '2011-11-03 13:43:26', 'Papelarias'),
(44, 1, '2011-11-03 13:43:26', 'Quitandas'),
(45, 1, '2011-11-03 13:43:26', 'Revistarias'),
(46, 1, '2011-11-03 13:43:26', 'Sex Shop'),
(47, 1, '2011-11-03 13:43:26', 'Sapatarias'),
(48, 1, '2011-11-03 13:43:26', 'Tapeçarias'),
(49, 1, '2011-11-03 13:43:26', 'Tinturarias'),
(50, 1, '2011-11-03 13:43:26', 'Tabacarias'),
(51, 1, '2011-11-03 13:43:26', 'Vídeo Locadoras'),
(52, 1, '2011-11-03 13:43:26', 'Vidraçarias'),
(53, 1, '2011-11-03 13:43:26', 'Bombonieres'),
(54, 1, '2011-11-03 13:43:26', 'Calhas'),
(55, 1, '2011-11-03 13:43:26', 'Carimbos'),
(56, 1, '2011-11-03 13:43:26', 'Água Mineral'),
(57, 1, '2011-11-03 13:43:26', 'Móveis'),
(58, 1, '2011-11-03 13:43:26', 'Molduras'),
(59, 1, '2011-11-03 13:43:26', 'Materiais para Construção'),
(60, 1, '2011-11-03 13:43:26', 'Produtos de Limpeza'),
(61, 1, '2011-11-03 13:50:57', 'Piercing'),
(62, 1, '2011-11-03 16:55:39', 'Portões'),
(63, 1, '2011-11-03 13:50:57', 'Quadros'),
(64, 1, '2011-11-03 13:50:57', 'Revestimentos'),
(65, 1, '2011-11-03 13:50:57', 'Toldos'),
(66, 1, '2011-11-03 13:50:57', 'Telhados'),
(67, 1, '2011-11-03 13:50:57', 'Tintas'),
(68, 1, '2011-11-03 13:50:57', 'Texturas'),
(69, 1, '2011-11-03 13:50:57', 'Trajes à Rigor'),
(70, 1, '2011-11-03 13:50:57', 'Utilidades para o Lar'),
(71, 1, '2011-11-03 13:50:57', 'Vídeo Games'),
(72, 1, '2011-11-03 13:50:57', 'Ar Condic. Residêncial'),
(73, 1, '2011-11-03 13:50:57', 'Construtoras'),
(74, 1, '2011-11-03 13:50:57', 'Acabamento'),
(75, 1, '2011-11-03 13:50:57', 'Estruturas'),
(76, 1, '2011-11-03 13:50:57', 'Forros'),
(77, 1, '2011-11-03 13:50:57', 'Gesso'),
(78, 1, '2011-11-03 13:50:57', 'Iluminação'),
(79, 1, '2011-11-03 13:50:57', 'Automação'),
(80, 1, '2011-11-03 13:50:57', 'Desentupidoras'),
(81, 1, '2011-11-03 13:50:57', 'Distribuidoras Água Mineral'),
(82, 1, '2011-11-03 13:50:57', 'Diversos'),
(83, 1, '2011-11-03 13:50:57', 'Distribuidoras de Gelo'),
(84, 1, '2011-11-03 13:50:57', 'Gerenciamento'),
(85, 1, '2011-11-03 13:50:57', 'Gestão'),
(86, 1, '2011-11-03 13:50:57', 'Jardinagem'),
(87, 1, '2011-11-03 13:50:57', 'Lustres'),
(88, 1, '2011-11-03 13:50:57', 'Locação de Equipamentos'),
(89, 1, '2011-11-03 13:50:57', 'Empilhadeiras'),
(90, 1, '2011-11-03 13:50:57', 'Equipamentos Segurança'),
(91, 1, '2011-11-03 13:50:57', 'Manutenção'),
(92, 1, '2011-11-03 13:50:57', 'Metalúrgicas'),
(93, 1, '2011-11-03 13:50:57', 'Arquitetura'),
(94, 1, '2011-11-03 13:50:57', 'Engenharia'),
(95, 1, '2011-11-03 13:50:57', 'Vestuário'),
(96, 1, '2011-11-03 13:50:57', 'Projetos'),
(97, 1, '2011-11-03 13:50:57', 'Telemensagens'),
(98, 1, '2011-11-03 13:50:57', 'Terceirização'),
(99, 1, '2011-11-03 13:50:57', 'Telefonia'),
(100, 1, '2011-11-03 13:50:57', 'Transportadoras'),
(101, 1, '2011-11-03 14:00:14', 'Turismo'),
(102, 1, '2011-11-03 14:00:14', 'Transporte Escolar'),
(103, 1, '2011-11-03 14:00:14', 'Telemarketing'),
(104, 1, '2011-11-03 14:00:14', 'Tatuagens'),
(105, 1, '2011-11-03 14:00:14', 'Usinagem'),
(106, 1, '2011-11-03 14:00:14', 'Viagens'),
(107, 1, '2011-11-03 14:00:14', 'Construção Civil'),
(108, 1, '2011-11-03 14:00:14', 'Dedetização'),
(109, 1, '2011-11-03 14:00:14', 'Confecções'),
(110, 1, '2011-11-03 14:00:14', 'Paisagismo'),
(111, 1, '2011-11-03 14:00:14', 'Piscinas'),
(112, 1, '2011-11-03 14:00:14', 'Refrigeração'),
(113, 1, '2011-11-03 14:00:14', 'Restaurações'),
(114, 1, '2011-11-03 14:00:14', 'Soldas'),
(115, 1, '2011-11-03 14:00:14', 'Segurança do Trabalho'),
(116, 1, '2011-11-03 14:00:14', 'Sistemas de Segurança'),
(117, 1, '2011-11-03 14:00:14', 'Seguros'),
(118, 1, '2011-11-03 14:00:14', 'Consórcios'),
(119, 1, '2011-11-03 14:00:14', 'Consultorias'),
(120, 1, '2011-11-03 14:00:14', 'Contabilidade'),
(121, 1, '2011-11-03 14:00:14', 'Cobranças'),
(122, 1, '2011-11-03 14:00:14', 'Assessorias'),
(123, 1, '2011-11-03 14:00:14', 'Empréstimos'),
(124, 1, '2011-11-03 14:00:14', 'Factoring'),
(125, 1, '2011-11-03 14:00:14', 'Pintura'),
(126, 1, '2011-11-03 14:00:14', 'Bicicletarias'),
(127, 1, '2011-11-03 14:00:14', 'Táxis'),
(128, 1, '2011-11-03 14:00:14', 'Instrumentos Musicais'),
(129, 1, '2011-11-03 14:00:14', 'Hortifruti'),
(130, 1, '2011-11-03 14:00:14', 'Casas de Ervas'),
(131, 1, '2011-11-03 14:00:14', 'Eletricistas'),
(132, 1, '2011-11-03 14:00:14', 'Motoboys'),
(133, 1, '2011-11-03 14:00:14', 'Azulegistas'),
(134, 1, '2011-11-03 14:00:14', 'Pedreiros'),
(135, 1, '2011-11-03 14:00:14', 'Advogados'),
(136, 1, '2011-11-03 14:00:14', 'Pintores'),
(137, 1, '2011-11-03 14:00:14', 'Brinquedos'),
(138, 1, '2011-11-03 14:00:14', 'Cartuchos para Impressoras'),
(139, 1, '2011-11-03 14:00:14', 'Games'),
(140, 1, '2011-11-03 14:00:14', 'Enxovais'),
(141, 1, '2011-11-03 14:02:06', 'Embalagens'),
(142, 1, '2011-11-03 14:02:06', 'Jogos'),
(143, 1, '2011-11-03 14:02:06', 'Presentes'),
(144, 1, '2011-11-03 14:02:06', 'Bordados'),
(145, 1, '2011-11-03 14:02:06', 'Bijuterias'),
(146, 1, '2011-11-03 14:02:06', 'Costureiras'),
(147, 1, '2011-11-03 14:02:06', 'Lembranças'),
(148, 1, '2011-11-03 14:02:06', 'Xerox'),
(149, 1, '2011-11-03 14:02:06', 'Tv a Cabo'),
(150, 1, '2011-11-03 14:02:06', 'Tv por Satélite'),
(151, 2, '2011-11-03 14:15:43', 'Adestramento'),
(152, 2, '2011-11-03 14:15:43', 'Aviculturas'),
(153, 2, '2011-11-03 14:15:43', 'Canil'),
(154, 2, '2011-11-03 14:15:43', 'Gatil'),
(155, 2, '2011-11-03 14:15:43', 'Lojas de Animais'),
(156, 2, '2011-11-03 14:15:43', 'Pet Shops'),
(157, 2, '2011-11-03 14:15:43', 'Veterinários'),
(158, 2, '2011-11-03 14:15:43', 'Tosa'),
(159, 2, '2011-11-03 14:15:43', 'Adoção de Animais'),
(160, 2, '2011-11-03 14:15:43', 'Doação de Animais'),
(161, 3, '2011-11-03 14:20:28', 'Alarmes Automotivos'),
(162, 3, '2011-11-03 14:20:28', 'Acessórios Automotivos'),
(163, 3, '2011-11-03 14:20:28', 'Agências de Veículos'),
(164, 3, '2011-11-03 14:20:28', 'Auto Elétricos'),
(165, 3, '2011-11-03 14:20:28', 'Auto Mecânicas'),
(166, 3, '2011-11-03 14:20:28', 'Auto Vidros'),
(167, 3, '2011-11-03 14:20:28', 'Ar Condic. Automotivo'),
(168, 3, '2011-11-03 14:20:28', 'Auto Escolas'),
(169, 3, '2011-11-03 14:20:28', 'Blindagem de Autos'),
(170, 3, '2011-11-03 14:20:28', 'Borracharias'),
(171, 3, '2011-11-03 14:20:28', 'Centros Automotivos'),
(172, 3, '2011-11-03 14:20:28', 'Concessionárias'),
(173, 3, '2011-11-03 14:20:28', 'Estacionamentos'),
(174, 3, '2011-11-03 14:20:28', 'Fretamento'),
(175, 3, '2011-11-03 14:20:28', 'Desmanches'),
(176, 3, '2011-11-03 16:55:55', 'Funilaria'),
(177, 3, '2011-11-03 14:20:28', 'Guinchos'),
(178, 3, '2011-11-03 14:20:28', 'Inspeção Veicular'),
(179, 3, '2011-11-03 14:20:28', 'Lava Rápidos'),
(180, 3, '2011-11-03 14:20:28', 'Locação de Veículos'),
(181, 3, '2011-11-03 14:20:49', 'Auto Tapeçarias'),
(182, 3, '2011-11-03 14:20:49', 'Auto Peças'),
(183, 4, '2011-11-03 14:24:14', 'Adesivos'),
(184, 4, '2011-11-03 14:24:14', 'Brindes'),
(185, 4, '2011-11-03 14:24:14', 'Gráficas'),
(186, 4, '2011-11-03 14:24:14', 'Impressos'),
(187, 4, '2011-11-03 14:24:14', 'Serigrafia'),
(188, 4, '2011-11-03 14:24:14', 'Impressão Digital'),
(189, 0, '0000-00-00 00:00:00', ''),
(190, 5, '2011-11-03 14:28:25', 'Acupuntura'),
(191, 5, '2011-11-03 14:28:25', 'Clínicas de Estética'),
(192, 5, '2011-11-03 14:28:25', 'Academias'),
(193, 5, '2011-11-03 14:28:25', 'Cosméticos'),
(194, 5, '2011-11-03 14:28:25', 'Cabelereiros'),
(195, 5, '2011-11-03 14:28:25', 'Depilação'),
(196, 5, '2011-11-03 14:28:25', 'Estética'),
(197, 5, '2011-11-03 14:28:25', 'Esteticistas'),
(198, 5, '2011-11-03 14:28:25', 'Escolas de Cabelereiros'),
(199, 5, '2011-11-03 14:28:25', 'Manicuries'),
(200, 5, '2011-11-03 14:28:25', 'Noivas'),
(201, 5, '2011-11-03 14:28:25', 'Pedicuries'),
(202, 5, '2011-11-03 14:28:25', 'Quiroterapia'),
(203, 5, '2011-11-03 14:28:25', 'Salões de Beleza'),
(204, 6, '2011-11-03 14:31:10', 'Associações'),
(205, 6, '2011-11-03 14:31:10', 'Bibliotecas'),
(206, 6, '2011-11-03 14:31:10', 'Clubes'),
(207, 6, '2011-11-03 14:31:10', 'Igrejas'),
(208, 6, '2011-11-03 14:31:10', 'Ongs'),
(209, 6, '2011-11-03 14:31:10', 'Teatros'),
(210, 7, '2011-11-03 14:35:07', 'Agências de Criação'),
(211, 7, '2011-11-03 14:35:07', 'Criação de Sites'),
(212, 7, '2011-11-03 14:35:07', 'Design Gráfico'),
(213, 7, '2011-11-03 14:35:07', 'Divulgação'),
(214, 7, '2011-11-03 14:35:07', 'Edição de Imagens'),
(215, 7, '2011-11-03 14:35:07', 'Edição de Vídeos'),
(216, 7, '2011-11-03 14:35:07', 'Estúdios Fotográficos'),
(217, 7, '2011-11-03 14:35:07', 'Fotografia'),
(218, 7, '2011-11-03 14:35:07', 'Filmagens'),
(219, 7, '2011-11-03 14:35:07', 'Fachadas'),
(220, 7, '2011-11-03 14:35:07', 'Hospedagem de Sites'),
(221, 7, '2011-11-03 14:35:07', 'Internet'),
(222, 7, '2011-11-03 14:35:07', 'Lojas Virtuais'),
(223, 7, '2011-11-03 14:35:07', 'Produção de Vídeos'),
(224, 7, '2011-11-03 14:35:07', 'Propaganda'),
(225, 7, '2011-11-03 14:35:07', 'Publicidade'),
(226, 7, '2011-11-03 14:35:07', 'Sites'),
(227, 7, '2011-11-03 14:35:07', 'Vídeos'),
(228, 7, '2011-11-03 14:35:07', 'Otimização de Sites'),
(229, 8, '2011-11-03 14:39:12', 'Aulas Particulares'),
(230, 8, '2011-11-03 14:39:12', 'Cursos'),
(231, 8, '2011-11-03 14:39:12', 'Colégios'),
(232, 8, '2011-11-03 14:39:12', 'Creches'),
(233, 8, '2011-11-03 14:39:12', 'Ensino a Distância'),
(234, 8, '2011-11-03 14:39:12', 'Escolas de Dança'),
(235, 8, '2011-11-03 14:39:12', 'Escolas de Idiomas'),
(236, 8, '2011-11-03 14:39:12', 'Escolas de Informática'),
(237, 8, '2011-11-03 14:39:12', 'Escolas de Música'),
(238, 8, '2011-11-03 14:39:12', 'Faculdades'),
(239, 8, '2011-11-03 14:39:12', 'Graduação'),
(240, 8, '2011-11-03 14:39:12', 'Professores'),
(241, 9, '2011-11-03 14:42:23', 'Bares'),
(242, 9, '2011-11-03 14:42:23', 'Churrascarias'),
(243, 9, '2011-11-03 14:42:23', 'Casas de Shows'),
(244, 9, '2011-11-03 14:42:23', 'Cafeterias'),
(245, 9, '2011-11-03 14:42:23', 'Choperias'),
(246, 9, '2011-11-03 14:42:23', 'Esfiharias'),
(247, 9, '2011-11-03 14:42:23', 'Gastronomia'),
(248, 9, '2011-11-03 14:42:23', 'Lanchonetes'),
(249, 9, '2011-11-03 14:42:23', 'Padarias'),
(250, 9, '2011-11-03 14:42:23', 'Panificadoras'),
(251, 9, '2011-11-03 14:42:23', 'Pastelarias'),
(252, 9, '2011-11-03 14:42:23', 'Pizzarias'),
(253, 9, '2011-11-03 14:42:23', 'Restaurantes'),
(254, 9, '2011-11-03 14:42:23', 'Rotisserias'),
(255, 9, '2011-11-03 14:42:23', 'Sorveterias'),
(256, 9, '2011-11-03 14:42:50', 'Self Service'),
(257, 10, '2011-11-03 14:47:06', 'Buffets'),
(258, 10, '2011-11-03 14:47:06', 'Buffets Infantil'),
(259, 10, '2011-11-03 14:47:06', 'Djs'),
(260, 10, '2011-11-03 14:47:06', 'Eventos'),
(261, 10, '2011-11-03 14:47:06', 'Festas'),
(262, 10, '2011-11-03 14:47:06', 'Grupos Musicais'),
(263, 10, '2011-11-03 14:47:06', 'Artistas'),
(264, 10, '2011-11-03 14:47:06', 'Chácaras'),
(265, 10, '2011-11-03 14:47:06', 'Materiais Festivos'),
(266, 10, '2011-11-03 14:47:06', 'Fantasias'),
(267, 10, '2011-11-03 16:56:08', 'Sítios'),
(268, 10, '2011-11-03 14:47:06', 'Salões de Festas'),
(269, 10, '2011-11-03 14:47:06', 'Aluguel de Brinquedos'),
(270, 10, '2011-11-03 14:47:06', 'Animadores de Festas'),
(271, 11, '2011-11-03 14:54:23', 'Clínicas'),
(272, 11, '2011-11-03 14:54:23', 'Dentistas'),
(273, 11, '2011-11-03 14:54:23', 'Massagistas'),
(274, 11, '2011-11-03 14:54:23', 'Odontologia'),
(275, 11, '2011-11-03 14:54:23', 'Oftalmologia'),
(276, 11, '2011-11-03 14:54:23', 'Ortopedia'),
(277, 11, '2011-11-03 14:54:23', 'Ortodontia'),
(278, 11, '2011-11-03 14:54:23', 'Podologia'),
(279, 11, '2011-11-03 14:54:23', 'Psicólogos'),
(280, 11, '2011-11-03 14:54:23', 'Psiquiatras'),
(281, 11, '2011-11-03 14:54:23', 'Saúde'),
(282, 11, '2011-11-03 14:54:23', 'Planos de Saúde'),
(283, 12, '2011-11-03 14:55:40', 'Hotéis'),
(284, 12, '2011-11-03 14:55:40', 'Hospedarias'),
(285, 12, '2011-11-03 14:55:40', 'Motéis'),
(286, 12, '2011-11-03 14:55:40', 'Pensões'),
(287, 12, '2011-11-03 14:55:40', 'Quartos'),
(288, 1, '2011-11-03 14:57:00', 'Casas de Carnes');

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
Denis Courcy

Denis Courcy

Posted
Posted

Oi 'Zek'

É isso que você quer?

SELECT DISTINCT A.`codAnuncio`,A.`tituloAnuncio`, A.`imgAnuncio`, GROUP_CONCAT(D.`nmCatPadrao` )
FROM `tbanuncio` AS A 
LEFT JOIN `tbrecomendanuncio` AS B ON B.`FK_Anuncio_RA` = A.`codAnuncio`
INNER JOIN `tbcategoriaanuncio` AS C ON C.`FK_Anuncio_CA` = A.`codAnuncio`
INNER JOIN `tbcategoriapadrao` AS D ON D.`codCatPadrao` = C.`FK_CatPd_CA`
WHERE A.`statusAnuncio` = 2 AND A.`statusFree` = 2 AND A.`codEntrega` = 1 
GROUP BY A.`FK_CatGeral_A`, A.`codAnuncio`, B.`FK_Anuncio_RA`
ORDER BY A.`codAnuncio` ASC;

Link to comment
Share on other sites
  • 0
Zek

Zek

Posted
  • Author
Posted
Oi 'Zek'

É isso que você quer?

SELECT DISTINCT A.`codAnuncio`,A.`tituloAnuncio`, A.`imgAnuncio`, GROUP_CONCAT(D.`nmCatPadrao` )
FROM `tbanuncio` AS A 
LEFT JOIN `tbrecomendanuncio` AS B ON B.`FK_Anuncio_RA` = A.`codAnuncio`
INNER JOIN `tbcategoriaanuncio` AS C ON C.`FK_Anuncio_CA` = A.`codAnuncio`
INNER JOIN `tbcategoriapadrao` AS D ON D.`codCatPadrao` = C.`FK_CatPd_CA`
WHERE A.`statusAnuncio` = 2 AND A.`statusFree` = 2 AND A.`codEntrega` = 1 
GROUP BY A.`FK_CatGeral_A`, A.`codAnuncio`, B.`FK_Anuncio_RA`
ORDER BY A.`codAnuncio` ASC;

Obrigado Denis, e desculpa pela demora na resposta, então, ainda não é isso, porq eu preciso que mostre o número de indicações que um anúncio teve, ou seja mostrar o COUNT mesmo, mas como eu disse no tópico ele esta somando os valores e não apresenta o resultado correto, mas obrigado pela ajuda.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to question listing


  • Forum Statistics

    • Total Topics
      152.2k
    • Total Posts
      652.1k
×
×
  • Create New...