Do a search for my site in two tables

Question

I need to do a search for my site in which I returned first the companies that have something similar to typed and, right below, show all registered products with something similar to typed.

I have two tables:

tbl_empresas
`id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(100) NOT NULL,
  `descricao` longtext,
  `imagem` varchar(255) DEFAULT NULL,
  `data_cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)

and

tbl_produtos
`id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(70) NOT NULL,
  `descricao` longtext,
  `aplicacao` varchar(255) DEFAULT NULL,
  `imagem` varchar(255) DEFAULT NULL,
  `codigo_original` varchar(50) NOT NULL,
  `data_cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tbl_categoria_id` varchar(11) NOT NULL,
  `tbl_empresa_id` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)

I tried to perform a survey using JOIN, but it would return me the two tables together, so I could not separate what is product and what is company in the survey visualization.

I wanted my research to show the companies first, then the products (without relationship) and it was possible to make a pagination later.

That would be possible?
If so, how would I need to do?

Answer 1

select name, Description from ( SELECT name, description, 1 order FROM tbl_companies Where Description like 'word%' UNION SELECT name, Description, 2 order FROM tbl_products WHERE Description LIKE 'word%' ) order by order;

Answer 2

You can join the result of two selects using the UNION ALL

Example:

SELECT nome, descricao, 'empresa' AS tipo FROM tbl_empresas
where descricao like 'palavra%';

UNION ALL

SELECT nome, descricao, 'produto' AS tipo FROM tbl_produtos
WHERE descricao LIKE 'palavra%';

See working on Sqlfiddle