Posts by rLinhares • 7,797 points
331 posts
-
2
votes1
answer153
viewsA: Insert with three tables in Mysql with PHP
You can solve this problem in two ways; the most practical would be to consult the latter id inserted at the time of the next insert: START TRANSACTION; INSERT INTO pessoas VALUES (NULL,…
-
5
votes3
answers323
viewsA: Group by age group
The error is in the use of CASE; the structure should be like this: CASE coluna_consultada WHEN 'valor_1' THEN 'retorno um' WHEN 'valor_2' THEN 'retorno dois' ELSE 'retorno tres' END AS…
-
1
votes1
answer532
viewsA: SELECT DISTINCT in JSON column in postgresql
In the postgresql you can YES group by columns of the json, you only need to specify the columns that should serve for grouping. In this post (en) you can see how; your consultation would look like…
-
1
votes2
answers123
viewsA: Query with date range with a variable
First you would need to control the initial and final dates, since you do not want to be based on the full month (from 1 to 30/31). Done this, just compare to colunadata with these two: SELECT *…
-
2
votes2
answers60
viewsA: Column type List
"not to leave the question unanswered.." Yes, if there is a list inside an object and it will be taken to the database, a second table must be created that will contain those items from the list;…
sql-serveranswered rLinhares 7,797 -
0
votes1
answer97
viewsA: Add another sum result
As stated in the comments, you should remove the group by; the results presented will be brought in only one line, since there is no division/grouping by id: SELECT DISTINCT…
-
0
votes3
answers140
viewsA: GROUP BY last registration per month and year how to do?
According to this question of SOEN, it is possible to use MAX along with GROUP BY; then you can do the following: SELECT id, valor, mes, ano, CONCAT(ano, mes) as anoMes FROM tabela GROUP BY id I…
-
0
votes1
answer59
viewsA: How to assemble a consult to bring the latest value based on month and year?
Whereas the query shown in the question is correct in relation to the relationship between fields, you can concatenate (Concat()) year and month and make a decreasing ordering of this result (ex…
-
2
votes2
answers271
viewsA: UPDATE with two conditions
From what I understand of " join the two in one", would be more or less that: UPDATE RLT005 SET CtrDatBaixa = CASE WHEN CtrDatBaixa BETWEEN '2018-12-15 00:00:00' AND '2018-12-15 23:59:59' THEN…
-
4
votes1
answer48
viewsA: How to get from last record to first
You can order your return through the order by: SELECT nome FROM tabela ORDER BY nome DESC…
-
1
votes1
answer579
viewsA: Change Google Maps default pin color
There are some different color markers that you can use; blue, red, purple, yellow, green. So you can use the structure that you put in the question and put one of these markers as icon: var…
-
2
votes1
answer95
viewsA: update with self relationship
Yes, it is possible! Considering the expected output example, you need to validate whether the item will be updated (CAT_ORIGEM) is the type "Son" And if there’s one of those "Register" that it’s…
-
2
votes3
answers91
viewsA: Help with SQL query with COUNT in multiple rows
The simplest way to do this is with a subconsultation; you create a select to return the total trip records of the searched line: SELECT p.id, c.nome, v.titulo, (select count(*) from passageiros p2…
-
3
votes1
answer87
viewsA: Multiple SQL query
That solves your problem: SELECT *, ( SELECT top 1 marca.* FROM marca WHERE marca.id = produtos.id ) AS nome_marca FROM produtos WHERE id = 5 The problem is that the way it was, the sql does not…
-
4
votes3
answers98
viewsA: With LEFT JOIN list the NOT IN
You can use the following: SELECT ID FROM usu LEFT JOIN usu_gerenciamento on ID = USUARIO_ID WHERE USUARIO_ID IS NULL; left Join filtering what does not occur in usu_gerenciamento.…
-
0
votes2
answers273
viewsA: How to select only people with a first name
An alternative would be a call to the function POSITION(); whereas the surname will always be separated by a space (), can use the following: SELECT * FROM pessoa WHERE POSITION(' ' in nome) > 0;…
-
1
votes1
answer118
views -
0
votes1
answer43
viewsA: Slow query performance
I can think of a few improvements, basically: take out the and of join; check these conditions at the junction increases much the amount of validations at the time of joining a table to another.…
-
5
votes2
answers203
viewsA: Explanation of recursive functioning
The main question would be "what your function wants to do?". What it does is decrease the number typed in one and subtract it from the final result, then: func(5) returns the result of func(4) - 5…
-
3
votes2
answers501
viewsA: Is it possible to run 2 queries at the same time in Mysql? Stored Procedure
I don’t understand why you need two updates, can do with only one, using case when: DELIMITER $ CREATE PROCEDURE modificaSalario2() BEGIN UPDATE Funcionario SET Salario = Salario * (CASE WHEN…
-
2
votes2
answers100
viewsA: Select with reference in several tables
Its structure is a little confused, but from what I understand, the three tables have the column codigo, which would be the field of union between them. Therefore, you must bring the columns…
-
0
votes1
answer46
viewsA: Join show later dates only
Whereas there are only the three statuses shown in the example (produzido, embalado, despachado), I suggest you always take the last occurrence of each id. If it is despachado, the item is finished;…
-
0
votes3
answers475
views -
1
votes1
answer60
viewsA: Error using ALIAS in select
You actually have two problems; the first one causes an error in execution because the alias is set between two asterisks instead of between quotation marks: select substr(titulo, 1, 20) AS…
-
0
votes4
answers79
viewsA: Find names in 2 different tables
You will need to use the UNION: SELECT adm_id AS id, nome FROM administradores WHERE nome = 'nome_pesquisado' UNION SELECT user_id AS id, nome FROM usuarios WHERE nome = 'nome_pesquisado' Thus, the…
-
5
votes2
answers555
viewsA: Command SQL PARTITION BY?
Basically the PARTITION BY creates a table with one or more partitions; in other words, physical tables are created that will be accessed through the specified table. Exemplifying, when creating the…
-
0
votes1
answer398
viewsA: Use several ternary conditions in php
You can even use this but, as you can see, the readability of the code is horrible. "Identando" the condition can be seen where the error is (in the control of the parentheses): echo ($estado ==…
-
1
votes2
answers66
viewsA: Reading an array 2 times
Alternatively, you can control using another repeat structure foreach ($teste as $c) { for (int $i=0; $i<2; $i++){ echo $c; } }
-
1
votes2
answers255
viewsA: Return the highest value between columns and the row ID of that highest value
I can even think of a solution but it would be gambiarra, I do not know if answers (by using union will not have such a good performance depending on the amount of data): SELECT id, val FROM (SELECT…
-
1
votes2
answers51
viewsA: Sort table values by Day and Month
To solve, the easiest way would be to convert the field to date type using to_date() and sort (by problem, your field should be varchar): SELECT b.id, b.codigo, a.codacesso, a.seqproduto,…
-
2
votes2
answers414
viewsA: Check if it contains characters in the string
Use the trim(), so spaces will be disregarded at the beginning and end of the string. var teste = string_usada.trim();…
-
1
votes1
answer90
views -
4
votes1
answer289
viewsA: How to change the Encounter in Java?
I believe you can do this when adding text to the list: JSONArray jsonarray = new JSONArray(jsonString); List municipios = new ArrayList(); for (int i = 0; i < jsonarray.length(); i++) {…
-
0
votes2
answers1208
viewsA: select month and year of a datetime field
The best way to do this would be to check the interval between dates. SELECT * FROM mytable WHERE date_column >= '2018-05-01' AND date_column <= '2018-05-31'; detail: worth giving a read in…
-
3
votes1
answer56
viewsA: Postgresql build error
I thought the problem would be in cast; went to create this fiddle and I realized the error message (42601: syntax error at or near "'day'") is related to alias, not to the extract. Removing the…
-
1
votes1
answer702
viewsA: Filter PHP and Mysql year and month
By default the bindValue passes the parameter as string, then you need to convert the received parameter in the query date; in addition, you are comparing year and month (year() and month()) with…
-
1
votes1
answer197
viewsA: Search item result in same line separated by comma
You can use the list()|: SELECT T1.ASO_ID, T1.RIS_ID, T3.RGR_GRUPO, LIST(T4.RIS_RISCO, ', ') FROM ASO_RISCOS T1 INNER JOIN GRUPO_RISCOS T2 ON (T1.RIS_ID = T2.RIS_ID) INNER JOIN RISCO_GRUPO T3 ON…
-
0
votes2
answers32
viewsA: sql return php data
You can use the CURDATE() instead of NOW() (now date + time): $data_hoje = "SELECT * FROM vendas WHERE DATE_FORMAT(dataCompra,'%d/%m/%Y') = CURDATE()"; You can use DATE_ADD() for this control; as…
-
1
votes1
answer108
viewsA: Double INSERT no stored
Makes no sense two Inserts at the same time, since this command is directed to a table; but you can have two in the same proc: DELIMITER // CREATE PROCEDURE proccomdoisinserts(IN valorX INT, IN…
-
0
votes3
answers2676
viewsA: When to use SET and SELECT?
For working with mysql, adopted the following practices also in sql server: utilise set for assigning value to control variables; utilise select for other cases. In other words, when I need to…
-
-1
votes1
answer129
views -
1
votes1
answer81
viewsA: Grab subquery ID field that only draws Mysql name
The error shown in the query is typing only; on the line causing the error (SELECT treNome FROM sistema_trecho treo WHERE tre.treId = voo.vooOrigemId) as treNomeOrigem, the "table" name is different…
-
6
votes2
answers260
viewsA: Search with case insensitive
The simplest/practical way is to also string query for the same format: if (material.name.toLowerCase().indexOf(query.toLowerCase()) != -1)
-
0
votes1
answer162
viewsA: LOAD DATA LOCAL INFILE catching 1000 first lines
Most likely your problem is in file size, not in the amount of processed lines. In this and in this other posts it is said to change the maximum size of the file to be read by changing the php.ini:…
-
1
votes1
answer23
views -
2
votes2
answers482
viewsA: Calculation of interest and fines in PHP
If the fine will be imposed only in case of delay, I believe that validate only if the payment date is higher than that of the maturity should resolve: $databx = new DateTime(); $datavc = new…
-
0
votes2
answers700
viewsA: Query with Left Join without returning duplicate value
Considering only the data presented in the question, you can group all returns in one column using Stuff(): SELECT distinct id, stuff(( select cast(',' as varchar(max)) + U.coluna from (select *…
-
2
votes1
answer182
viewsA: Select with PIVOT always returns null
You need to change the columns to be searched within the pivot: DECLARE @registros as table ( ID int, Campo varchar(250), Valor varchar(250) ) INSERT INTO @registros VALUES (1,'Pesquisar…
-
1
votes2
answers746
viewsA: Count how many fields are empty on a MYSQL line
Based on what I understand (how many fields of each user were not filled in), I made the following code (take a look at this functional fiddle): create table tabela_colunas (id int, coluna…
-
0
votes2
answers784
viewsA: Select current date comparison with exact date 1 month ago
From what I understand you need the totals per day, type total day 10/10/2018, total day 09/10/2018; that is, the comparison should be made by the day instead of -24h (this way you take part of the…