How to perform query in two tables by doing data combination

Asked

Viewed 38 times

0

I have two tables:

material

id | categoria | subcategoria | codigo
1  |     3     | 15           | FS-5600
2  |     0     | 16           | FA-4500

subcategory

id  | descricao | id_categoria | id_produto
1   |  Alasca   | 3            | 2
4   |  Aragon   | 3            | 2
16  |  A. Luiza | 7            | 1
15  |  Astoria  | 8            | 1

I have a search field, where the user can search for both the description and the code. As we can see, code and description are in separate tables, but when performing a certain search, I need both to be returned.

How to be mounted to query?

php mysql

  • But Voce wants to return what? Product? Description of subcategory?

  • @Danillovictor, description and code.

  • the code column is referencing which column of the table subcategory?

  • @Danillovictor, we can continue this discussion via chat?

  • Your question is unclear, what should I search for? what should I return for a particular survey?

  • Peter, I wish to return code and description. "As we can see, code and description are in separate tables, but when performing a certain search, I need both to be returned." @Pedroh.

  • You can use Inner Join, it is a common feature for these cases.

  • the error is saying that there is no code column in the table subcategory. So I am saying, the source column of the material table is referencing which column of the table subcategory?

Show 3 more comments

1 answer

0

If I understand the question correctly you want the user to enter a term in a search field and the system to bring both the codes and descriptions that match the search.

Assuming the link between the tables is id and id_product

SELECT * FROM material as m JOIN subcategoria a s ON m.id = s.id_produto WHERE m.codigo LIKE 'termoAPesquisar%' OR s.descricao LIKE 'termoAPesquisar%';

You can also connect the JOIN by the coded columns.subcategory and subcategory.id, but in this case when the query is by product code the system will not return all subcategories. So for the tests you went through and for what I understood I think the ideal is the link by product

Browser other questions tagged php mysql

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