Search all records that exist in another table at least once

Question

Hello, I’m having trouble making the following query:

I have 3 tables: user_categories, main budget and orcamentos_new

When a user creates a budget, the system creates a record in main budget with the key information being: Who created the budget, completion date, status, etc.

On the table orcamentos_new is the specification of each item, being: item name (ex: inks), quantity, details and its category (paints => paint, for example) and the registration ID in main budget

The question is: in the table user_categories is safe from supplier categories, and this can be various types, such as paints, glass, lift etc

So what I need, is that when a buyer submit the quote (which may contain multiple categories) all suppliers (user_categories) that have at least ONE budget category, be returned in QUERY

So I have this:

usuarios_categorias
ID_user | ID_categoria
1         1 (tintas)
1         2 (vidros)
1         3 (elevador)


orcamentos_novo
Titulo  | ID_categoria
Vidros    2
Calhas    5
Piscina   8

Note that in the user_categories the user has the category Glass, but does not have the others, but is to return all records (Glass, gutters, pools) because it has at least one category

I’m not able to create a QUERY for this, what I currently have:

SELECT COUNT(*) as QTD_orc FROM orcamentos_principal
JOIN orcamentos_novo
ON orcamentos_novo.id_orcamento = orcamentos_principal.id
WHERE orcamentos_principal.status = 'ativo'

It searches all records but does not check if the user has at least one category

NOTE: I am the supplier, when I enter the page of available budgets he will have to fetch all budgets that I have at least ONE category, understand? then I need to return all the records of main budget where I have at least one of the categories of orcamentos_new associated with him

Answer 1

Doing directly in the query would look like this:

SELECT *
  FROM orcamentos_principais op
 WHERE op.status = 'ativo'
   AND EXISTS(SELECT 1
                FROM orcamentos_novo on
                     INNER JOIN usuarios_categorias uc ON uc.id_categoria = on.id_categoria
               WHERE on.id_orcamento = op.id_orcamento
                 AND uc.id_user = 1) -- Aqui você substitui pelo seu ID

Answer 2

Supposing you made the inquiry and ordered the products:

produto - categoria
arroz   - comida
feifão  - comida

And this is in an array:

$arr = [
   1 => [
       'produto' => 'arroz',
       'categoria' => 'comida'
   ],
   2 => [
       'produto' => 'feijao',
       'categoria' => 'comida'
   ]
];

Implode the category column of the array into a string:

$categorias_cols = array_column($arr, 'categoria');

$categorias_str = implode('","', $categorias_col);

That will be the answer:

comida","comida

Concatene:

$categorias_str_ok = '"'.$categorias_str.'"';

Query by users using Mysql’s IN function:

'SELECT * FROM sua_tabela_de_usuarios WHERE campo_da_cagoria IN('.$categorias_str_ok.')';

This query will bring all users who have a relationship with the array categories.

I believe that this is what you need, has how to do directly in the query, but I won’t be able to say how.