How to perform query on two tables by combining data

Navigation
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 given search, I need both to be returned.

How should the query be mounted?

    
asked by anonymous 13.10.2017 / 17:33

1 answer

0

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

Assuming the connection between the tables is id and product_id 

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 bind JOIN to the columns code.subcategory and subcategory.id, but in this case when the query is by product code the system will not return all subcategories. Then by the infos that passed and by what I understood I think the ideal is the connection by id_producto

    
14.10.2017 / 16:52
Call to undefined method Illuminate \ Database \ Query \ JoinClause :: whereBetween () Laravel 5.2 What is the correct type for storing phone and address? [duplicate]