Do a search for my site in two tables

0

I need to do a search for my site where I would first get back to businesses that have something similar to the one typed and, below, show all registered products with something similar to the typed one.

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 do a search using JOIN, but it would return the two tables together, so I would not be able to separate what is product and what is company in the search view.

I wanted my research to show businesses first, then products (no relationship), and it was possible to do paging later.

Would that be possible? If so, how would I need to do it?

    
asked by anonymous 30.09.2015 / 14:59

2 answers

0

You can join the result of two selects using 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 in SQLFiddle

    
30.09.2015 / 15:13
0

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

    
01.02.2018 / 14:25