Questions tagged [query]
Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).
1,509
questions
0
votes
1
answer
277
views
MySQL query: Detect different rows between two tables and show differences in result table
I have two tables in a MySQL database.
Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3
I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
- 157
0
votes
1
answer
46
views
LEFT JOIN not return right value
I am trying to join 3 tables with it foreign keys, but for some reason one of the return value from the query doesn't return correct when I use LEFT JOIN for the 3 tables but I removed the one that ...
- 127
1
vote
1
answer
455
views
Multi dimensional JSON Array SQL Query
I'm struggling to write an appropriate query for my data ->
"schools":[
{
"org_symbol":"School 1",
"criteria":[
[
...
- 11
1
vote
3
answers
884
views
MySQL: Improve performance on large table for dynamically built query with composite index correctly
Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows ...
- 215
-1
votes
1
answer
75
views
How to Verify Backup Archive in MySQL
I would like to know if there is any way to verify MySQL backup files. (Check if the file is valid or not for restore). Thanks!!
1
vote
1
answer
1k
views
Query optimisation to remove nested loop
I have below query which is having high Sub-tree cost. I have created appropriate Indexes for for the query which are getting seek but, still the sub-tree cost in high.
however, I can see nested Loop (...
- 33
2
votes
1
answer
980
views
Why SERIALIZABLE level does not lock the table for other session in SQL Server
I want to Prevent concurrency in my table for Delete, Insert, Update, and Select
so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
-3
votes
1
answer
631
views
Retrieving range of values from a lookup table using SQL
I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
1
vote
2
answers
161
views
Getting the total size of rows with same ids across multiple tables restricted to a particular user id
I'm trying to calculate all the projects size of one user across multiple tables.
a unique project is separated into 4 tables with multiple rows assigned to it.
for the moment I'm only able to ...
- 133
0
votes
0
answers
26
views
how to print message 'included' along with limit_1 value, when limit_1 is selected with condition
select max(Contract_Num) as contract_num,
max(Lic_Plate_Num) as lic_plate_num,
max(cast(Revision_Eff_Date as date)) as rev_eff_date,
max(contract_name) as cn_name,
...
1
vote
0
answers
22
views
Finding sub-groups by relationships within groups
In this example, two tables define the groups and the relationship of persons within each group
CREATE TABLE groups
(
group_id int(11) unsigned NOT NULL,
person_id int(11) unsigned NOT NULL,
INDEX(...
- 4,479
0
votes
1
answer
43
views
select row matching exact list of uids
Given table below
id | groupid | memberid
1 | g1 | m1
2 | g1 | m2
3 | g2 | m1
4 | g2 | m2
5 | g2 | m3
I'm trying to build a query where I want to get the groupid ...
- 11
1
vote
2
answers
31
views
How do you check if two tags exists in a tag_map GROUP By article_id?
In a fairly simple tag_map,
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB;
How can I ...
- 4,479
0
votes
1
answer
155
views
How to display last record per parameter?
I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites.
I'm having trouble figuring out how to ALSO create a column that displays the last ...
0
votes
1
answer
694
views
Add new column based on conditions and the value difference of other column
I think I might be overlooking something obvious here. I'm not asking someone to write the whole code for me, I just need a hint or a link to a similar case.
Thanks in advance...
My query:
select ...
- 127
0
votes
1
answer
34
views
Can't get this GROUP BY or DISTINCT right
On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select ...
- 195
0
votes
1
answer
34
views
Find records with join matching all join IDs
Suppose to have the following database setup:
Users (
id int primary key,
name varchar(255)
)
OrgUnits (
id int primary key,
name varchar(255)
)
Users_OrgUnits (
user_id int,
orgunit_id ...
- 169
0
votes
1
answer
26
views
Better ways of concating the value of columns
I have a table with below structure and sample data:
create table CUSTOMER_TEST
(
customer_num Number,
rel_one Number,
rel_two Number,
rel_three Number,
rel_four Number
)
...
- 1,402
3
votes
2
answers
1k
views
MySQL: How to optimize a certain SELECT statement which causes very high load?
Having a table with 25.000.000 entries, I have the following query which is started every ~ every 2 seconds which causes very high loads (up to 40 AAS in Amazon AWS). The execution takes 20 seconds up ...
- 215
1
vote
1
answer
4k
views
Syntax error in a simple SQL query [closed]
I am using pgAdmin to write a simple SQL INNER JOIN query. However, when I run the code it only will run the first line and give me a syntax error shown below. But, I can select the whole code and it ...
1
vote
0
answers
348
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
3
votes
2
answers
1k
views
How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column
db admins, I require your help!
My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this ...
- 33
3
votes
1
answer
1k
views
Postgres order by with multiple columns
So imagine I have a result set that looks something like this:
+----+------------+------------+
| Id | Date_One | Date_Two |
+----+------------+------------+
| 1 | 2022-05-12 | null |
| 2 |...
- 143
1
vote
0
answers
289
views
MS Access SQL - Left Join (or sub-query?) most recent related record
Hello Database Administrators.
Thank you so much for your help with this issue. It blows my mind that something so simple can be so complicated.
I have an MS Access database with two tables.
The first ...
- 11
0
votes
1
answer
216
views
How to query nested unamed jsonb objects and order?
have an audits column (jsonb) seems like:
{{"status": "pending", "updated_at": "2022-06-20T15:58:54-03:00"},{"status": "active", "...
1
vote
1
answer
35
views
How can I correct this mysql join query and put 1 to many in the same row?
This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to ...
- 113
4
votes
4
answers
5k
views
Select only rows where all values in a column match a value for an id
Given the table
seq_id
seq_status
1
A
1
B
2
A
2
A
3
A
4
B
5
C
I want to select the distinct seq_id's where the seq_status is A
(2 and 3) and not return if one of the values is different
so ...
- 49
2
votes
1
answer
982
views
Multiple inner JOIN on the same table [closed]
I have this table 'individu'
and this table
I want to do a select and get in the query, the 'nom' and 'prenom' for iid1 and iid2.
For the moment my query is
SELECT * FROM lien
inner join individu ...
- 21
1
vote
2
answers
1k
views
Convert Long list of IN clause into a temporary table
Instead of using a list of items for IN Clause I'm trying to use a temporary table.
The code I used previously.
SELECT * FROM table1 AS t1
WHERE itemid in ('3363134,5189076, ...)
The code I'...
- 111
1
vote
1
answer
3k
views
Get column name and column type in the same query in the correct order using psycopg2
I have a postgresql database and I am querying it using psycopg2 and I need to get the data in the correct order as in table I have found different ways to get the column names from table and column ...
- 11
0
votes
0
answers
35
views
Keeping a table up to date that has been created using a DB Query
I have created a new table view for customers, so that when they log in, they only see the information that they need to see...
I used the Query:
CREATE TABLE IF NOT EXISTS Customer( SELECT ColumnA, ...
- 167
1
vote
2
answers
247
views
Applying conditional statement in Oracle DB
In the Oracle DB there are three tables, see below.
Table: 'People'
Table: 'Dogs'
Table: 'Cats'
Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4
Depending on the result of a ...
- 177
1
vote
1
answer
100
views
Percentage based scan on a timeseries data
I have 20 GB csv file with 650 million rows. The data looks like this:
trade_time,price
2020-01-01 00:00:01.481,7189.42
2020-01-01 00:00:01.708,7189.42
2020-01-01 00:00:06.290,7189.5
2020-01-01 00:00:...
- 13
2
votes
2
answers
449
views
Can Somebody help me understand this query
The below SQL query gets all the ids which have different last names.
select *
from names a
WHERE a.first_name in (
select b.first_name
from names b
WHERE a.last_name<>b.last_name
)
I am ...
- 129
1
vote
0
answers
36
views
Getting totals from different tables to create overall total
I currently have 2 tables TableA and TableB` both within the same MYSQL Database.
I am trying to find a way to take totals from both tables and add them together to give a definitive total.
The tables ...
- 167
0
votes
1
answer
700
views
How to compute the average score for each user, by time interval?
I have a table User that only has an id and a name. A user has many Games, which is a table that contains an id, a score, inserted_at and a user_id referencing the User table. I would like that my ...
- 375
5
votes
1
answer
1k
views
Inconsistent keyset pagination when using (timestamp, uuid) fields
I am using the keyset pagination method for uuids on my Postgres database described in this post:
How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?
However, I have ...
- 51
0
votes
1
answer
44
views
How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?
I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded.
Example Data:
CREATE TABLE ...
- 13
-3
votes
1
answer
425
views
Import 5gb sql file into MYSQL database [duplicate]
i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname <file.sql command ...
- 1
0
votes
1
answer
422
views
Amount of clients that have subscription by years and month
I got 2 tables as follow
CREATE TABLE subscriptions(
client_id INT,
open_date DATE,
close_date DATE
);
CREATE TABLE calendar(date DATE);
subscription table contains data about clients ...
- 1
1
vote
1
answer
165
views
A query containing multiple joins becomes too slow when adding one more condition after the joins
I have this query that finishes in less than a second on average:
SELECT
a.trea_code,
a.fin_year,
a.bill_no AS tran_id,
((('Transfer FROM ' :: text || (a.ddo_code) :: text) || 'ON Bill ...
- 13
0
votes
2
answers
813
views
SQL query to return rows whose column has a common value
How can I query a Microsoft SQL table to return only the InstNum that has all Profile types C or U?
Please see my table structure below.
InstNum
Profile
001
U
001
C
001
U
002
U
002
U
002
U
003
...
- 3
-2
votes
1
answer
2k
views
Row_number to not increment if values on three columns are the same from the row before
Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic:
...
- 617
1
vote
1
answer
3k
views
Postgres "unnesting" multiple json arrays simultaneouesly with different depths
I am trying to explode/unnest a row of jsonb arrays into individual rows and keep any rows that would result in nulls.
Fiddle: https://www.db-fiddle.com/f/pgd6E5yKf8NzPtgboeoC6c/7
Input table: (I am ...
- 113
0
votes
1
answer
4k
views
MySQL - Getting the percentage of an occurrence
I'll do my best to explain what I'm trying to accomplish.
I have a table called "Actions" and in Actions I have a column called "Resolution". In resolution some the entries, as an ...
- 103
0
votes
2
answers
83
views
SQL Server - Finding Parent Source from T-SQL Snippet
The environments for which I'm responsible have some pretty substantial plan non-reuse challenges. I've run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine ...
- 137
3
votes
2
answers
22k
views
How to Join two tables with SUM and GROUP BY in SQL server
I have 2 tables
product
id
designation
1
coca
2
Pepsi
3
Fanta
4
Seven
5
Eight
2)sub_product
product_id
name
quantity
1
sm
10
1
lg
10
1
xl
20
2
1L
10
2
2L
20
2
5L
20
3
Ty
10
3
Sa
20
4
...
1
vote
1
answer
288
views
Microsoft SQL server management studio : insert label in SQL query results
I'm working with SQL server, using Microsoft SQL Management Studio.
I'm writing quite some SQL queries, one after the other.
This causes a list of results, which are quite difficult to distinguish, as ...
- 523
0
votes
1
answer
1k
views
SQL query to group results on matching record ID
I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
0
votes
1
answer
1k
views
Forcing a sequential scan over the index scan
I have this query
select
s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from
part,supplier,partsupp,nation,region
where
p_partkey=ps_partkey
and
s_suppkey=...
- 225