Problem to join two Selects in one instruction

Asked

Viewed 1,020 times

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 together select 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
;
mysql

  • 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.

  • 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

  • @Alexandrepreviatti not necessarily. vc can solve with alias and padding values, and use positional syntax instead of names.

2 answers

1

You have two ways to do this. one is by using the JOIN and another would be a select with SubQuery.

Answers:

Subquery

    SELECT perfil_usuario_unificado.nome_fantasia, perfil_usuario_unificado.descricao_emp,
        perfil_usuario_unificado.cnpj, identificacao_acesso.id,
(select 
COUNT(gostei_nao_gostei) FROM interacao_social
WHERE para_id_ia = '2' AND gostei_nao_gostei = '0'
and  --- aqui tem que haver uma ligação entre as tabelas) AS quantidade

     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;

The JOIN in which case it would be a little more complicated because you would have to group your data.

Give more details of the key fields of your tables if you need a better answer.

  • Hello, I also think that the best solution goes through subquery just need to know the link of the inter-social table to the other tables.

0

SELECT perfil_usuario_unificado.nome_fantasia, perfil_usuario_unificado.descricao_emp,
    perfil_usuario_unificado.cnpj, identificacao_acesso.id,
    COUNT(gostei_nao_gostei) AS quantidade -- segundo select
  FROM perfil_usuario_unificado,
     interacao_social -- segundo select
  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%') 
    AND para_id_ia = '2' AND gostei_nao_gostei = '0'; -- segundo select
 ORDER BY perfil_usuario_unificado.nome_fantasia ASC;

  • For better formatting, use markdown when formatting the code, or simply click {} with the selected code.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.