1
I need help with an SQL statement for the Mysql database.
The select
below returns me data coming from two tables. I use for a search system that I am developing.
SELECT perfil_usuario_unificado.nome_fantasia
,perfil_usuario_unificado.descricao_emp
,perfil_usuario_unificado.cnpj
,identificacao_acesso.id
FROM perfil_usuario_unificado
INNER JOIN identificacao_acesso
ON perfil_usuario_unificado.id_ia = identificacao_acesso.id
INNER JOIN b_tag_pesquisa
ON perfil_usuario_unificado.id_ia = b_tag_pesquisa.id_ia
WHERE b_tag_pesquisa.tag_pesquisa LIKE ('%WEB%')
ORDER BY perfil_usuario_unificado.nome_fantasia ASC;
Already the select
below makes a count on a given table field. Use to count how many "like" has a given record.
SELECT COUNT(gostei_nao_gostei) AS quantidade
FROM interacao_social
WHERE para_id_ia = '2'
AND gostei_nao_gostei = '0';
Problem:
I need to put the two togetherselect
in the same instruction, but I don’t know how to do.
Follow the diagrams of the tables:
CREATE TABLE `identificacao_acesso` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`niv` VARCHAR(50) NULL DEFAULT NULL,
`usuario` VARCHAR(50) NULL DEFAULT NULL,
`senha` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
CREATE TABLE `interacao_social` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`de_id_ia` INT(10) NULL DEFAULT '0',
`para_id_ia` INT(10) NULL DEFAULT '0',
`gostei_nao_gostei` INT(1) NULL DEFAULT '0',
`check_in` INT(1) NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10
;
CREATE TABLE `b_tag_pesquisa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`id_ia` INT(10) NULL DEFAULT NULL,
`tag_pesquisa` VARCHAR(500) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COMMENT='tabela de tag de pesquisa'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
CREATE TABLE `perfil_usuario_unificado` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`id_ia` INT(10) NULL DEFAULT NULL,
`cnpj` VARCHAR(14) NULL DEFAULT NULL,
`razao_social` VARCHAR(80) NULL DEFAULT NULL,
`nome_fantasia` VARCHAR(50) NULL DEFAULT NULL,
`descricao_emp` VARCHAR(200) NULL DEFAULT NULL,
`cpf` VARCHAR(50) NULL DEFAULT NULL,
`nome_pf` VARCHAR(50) NULL DEFAULT NULL,
`cep` INT(8) NULL DEFAULT NULL,
`endereco` VARCHAR(50) NULL DEFAULT NULL,
`complemento_end` VARCHAR(25) NULL DEFAULT NULL,
`numero_end` VARCHAR(10) NULL DEFAULT NULL,
`bairro` VARCHAR(25) NULL DEFAULT NULL,
`cidade` VARCHAR(25) NULL DEFAULT NULL,
`estado` CHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
Diego, I even did some research and such and found nothing, I believe that can not join two selects the way you want not. Taking advantage here leave my opinion: I recommend you divide this into two queries because it is already super big your query and the more processing it will take.
– Viniam
It is not possible to join these two selects, to use the UNION (union of selects) the returned fields must be the same in the two selects
– Alexandre Previatti
@Alexandrepreviatti not necessarily. vc can solve with alias and padding values, and use positional syntax instead of names.
– Bacco