Search for all records that exist in another table at least once

Navigation
0

Hello, I'm having trouble doing the following query:

I have 3 tables: users_categories , main_bucks and new_bucks

When a user creates a budget, the system creates a record in primary_budget with the main information, which is: Who created the budget, end date, status, etc.

In the new_counts table, the specification for each item is: item name (eg inks), quantity, details and category of the item (paint => painting) Record ID in primary orcamentos

The question is: in the table users_categories the categories of the supplier are saved, and this can be several types, such as paints, glasses,

So what I need is that when a buyer submits the budget (which can contain multiple categories) all suppliers ( category_users ) that have at least ONE budget category, is returned in QUERY

Then I have the following: 

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

Notice that in users_categories the user has the category Glasses, but does not have the others, however it is to return all the records (Glasses, gutters, pools) because it has at least one category

I can not figure out 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 fetches 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 budgets available he will have to seek all budgets that I have at least ONE category, you see? so I need to return all the records for primary_budgets where I have at least one of the new_budgets categories associated with it     

asked by anonymous 19.09.2017 / 20:07

2 answers

1

Doing directly in 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
    
19.09.2017 / 20:39
0

Assuming you have queried and requested 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'
   ]
];

Implements the column category of the array into a string: 

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

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

This will be the answer: 

comida","comida

Concatenate correctly: 

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

Query for users using the MySQL IN function: 

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

This query will bring all users related to array categories.

I think this is what you need, you can do it right in the query, but I will not know how to say it.

    
19.09.2017 / 20:34
Run automatic click on form input How to create directory and upload file