Do a search for my site in two tables

Asked

Viewed 57 times

0

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?

  • Select from the company table and then use UNION with select from the product table would help you?

  • I hadn’t tried UNION, I’ll take a test here

2 answers

0

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;

0


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

  • select worked, but I can distinguish these tables when showing in the view? or it would be all together anyway?

  • You can do the distinction for a column, I’ll edit the answer.

  • @Lucas worked?

  • is almost working, missing join with Where

  • But just put the where, young man. I edited the answer

  • I managed to do with Where, but I had a problem with "Undefined index: Description" when showing

  • Okay, thank you very much!

Show 2 more comments

Browser other questions tagged

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