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).

Filter by
Sorted by
Tagged with
1 vote
1 answer
461 views

Unique array values for this string_to_array

This is a follow-up to: Best way to map different JSON keys to same target columns Based on these sample tables: data_providers: id | field_map -------------- 1 | {"segments": "...
  • 203
20 votes
6 answers
3k views

Is there a generic term for tables and views?

I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso. As far as I understand, 'entity' is ...
1 vote
1 answer
70 views

How to address aggregate fanout in the below query

I have a table user. Every user has a one-to-many relationships with topic_likes and topic_dislikes. I want to query for a user and have their one-to-many relationships exist in their own column in ...
  • 123
0 votes
1 answer
392 views

FQN columns clash in HAVING clause - Unknown column error

Having this MySQL query SELECT `books`.* FROM `books` LEFT JOIN `authors` AS `author` ON (`books`.`author_id` = `author`.`id`) LEFT JOIN `publishers` AS `publisher` ON (`books`.`publisher_id`...
  • 101
2 votes
2 answers
615 views

Can you include the columns of two separate tables in the result of a sql query?

I am trying to order the results of a sql query of one table using the column of another table. Is it possible to include this column from a different table in my results? For example, this query uses ...
  • 127
0 votes
2 answers
1k views

Postgres: How to automatically fix date/time field value out of range inside the query

How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar? SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-02-31 ...
  • 101
0 votes
1 answer
38 views

What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

ERD for my simple db design: I want to order children based on their parent's last name. I am trying to use the following query: SELECT * FROM children WHERE parent_id IN (SELECT parent_id ...
  • 127
0 votes
2 answers
64 views

Fastest query to process data in small batches without repetition

I have java app that is using MySQL in the backend. I have the following table: A = int, B = varchar, C = timestamp A | B | C 1 | 100 | 2022-03-01 12:00:00 2 | 200 | 2022-03-01 12:00:01 3 | 100 | ...
2 votes
1 answer
371 views

Do UPDATEs block SELECTs?

I need to query a frequently updated record, does it wait until all updates finish? /updateUser/1 Many requests are sent UPDATE users SET count = count + 1 WHERE id = 1 LIMIT 1 /getUser/1 While the ...
-2 votes
2 answers
42 views

Get count of unmapped records when using a mapping table

I have 3 tables as such Product Product ID Product Details 1 ... 2 ... 3 ... Vendor Vendor ID Vendor Details 1 ... 2 ... 3 ... Product Vendors Product ID Vendor ID 1 1 2 1 1 2 2 2 3 2 ...
  • 107
4 votes
1 answer
108 views

Run multiple, expensive, single result queries and return a single row

I have a lot of fairly large datasets in a MS SQL table, and I want to get a bunch of data about. For example, let's say I have Name Age Height Weight Gender Country Person 23 186 79 Male GB ...
  • 141
1 vote
0 answers
300 views

SQL Server- Duplicate rows n times for all tables in a database

I have a db with over 150 tables which I need to artificially inflate for stress-testing. So basically I've built an app which extracts the said db, but am currently working with a snippet of what the ...
1 vote
2 answers
2k views

SQL (Postgres): query array Column which must be a subset of an array

I have a table "article" and for each entry there are some "tags" stored (array column) name | tags test1 | {t1,t3} test2 | {t2,t3} test3 | {t3} test4 | {t1,t5} test5 | {...
2 votes
1 answer
172 views

Getting how many days users have logged in during a month (same day counts as 1 log in)

Let's see if you can help me with a query. I want to know how many times users have logged in during a month. If I have this data: id_log id_user timestamp 1 1 2022-02-22 00:00:00 2 1 2022-02-22 15:...
  • 23
0 votes
1 answer
830 views

How to handle possible NULL in a Trigger Updating a table without a Primary Key

I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
0 votes
1 answer
31 views

How to run merge statement based on logic of joined table?

When I try to run a query like the following: MERGE INTO MYTABLE_A AS MERGE_TARGET USING ( SELECT MYTABLE_A.A, MYTABLE_B.B, MYTABLE_A.C, MYTABLE_D, MYTABLE_E FROM MYTABLE_A ...
  • 265
0 votes
0 answers
13 views

Query to Include All Records

I am trying to create an excel report and am running into an issue that I am most likely overthinking. What I am trying to do is create multiple pivot tables to show me my overall inventory and then a ...
  • 168
0 votes
1 answer
263 views

WordPress and WooCommerce very slow query

After a migration to a new WooCommerce install, but we are experiencing very sow queries. Sometimes more than 1 minte. Currently this query is fired. This is fired in 0.7253s EXPLAIN SELECT t.*, tt.*, ...
  • 1
-2 votes
1 answer
90 views

how to perform inner join on two table where i'am able to retrieve all data?

I need help with a MySQL query. bar_opening_details has closing & opening stock bar_recvd_details has received the stock When I receive purchased stock, I insert it into bar_recvd_details. Next, ...
  • 23
0 votes
1 answer
70 views

SQL Server query consolidating each database file data in to one row

I have this Dataset base of this query below SELECT DB_NAME() AS dbname , size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', ...
-1 votes
1 answer
648 views

SQL query to join on maximum value on a per-row basis

I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers ...
-1 votes
2 answers
212 views

To inquire about the query complexity of the following query in SQL

I would like to inquire about the complexity of the following example query in SQL: SELECT id from DB where Country = "India" AND Size = "Large" That is, taking the AND of ...
0 votes
1 answer
64 views

How to join 2 tables and fill non matching entries with a date

A company buys parts from other companies. To not overload their servers, every 5 minutes the price of 1 part is checked to see if a part has changed price. So a table contains the last time a part ...
  • 25
1 vote
1 answer
22 views

query relational tag data, grouping by a different metric

Suppose I have the following schema: CREATE TABLE tag_group ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, ); CREATE TABLE tag ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, ...
  • 111
0 votes
1 answer
188 views

how to use distinct on to avoid aggregating column in this query?

what I am trying to do is I want to calculate the quantity for the inventory the formula for that is receive_order_entries.quantity+ the sum of all inventory_transactions.quantity for that inventory ...
  • 13
6 votes
2 answers
296 views

Select all rows after first x rows containing NULLs

I have table src. In this table, each Product-Country combination might contain NULL in the Country column for the first x rows. How can I SELECT rows for each Product-Country combination, only rows ...
0 votes
0 answers
40 views

Is there a purpose for UPDATE ... WHERE but with same values?

I am translating some code from PHP to Python, and I came upon this interesting snippet: $sql11 = "UPDATE tbl_pas SET api_id_pas='" . $api['kd_pas'] . "' WHERE api_id_pas='&...
  • 187
0 votes
0 answers
60 views

Query is not returning array of json response

I am trying to get the array of json response in postgresql. Here is the query which I was trying: select array_to_json(array_agg(row_to_json(componentLevels))) from ( select q1_sales_tracking_q1_req ...
0 votes
1 answer
106 views

SQL - select latest 100 records and group by a column that is not the key or date

I have a table with a few columns, among which date and state. Neither the date or the state are a primary key. I would like to see how many of each "state" there are in the last 100 records....
  • 167
0 votes
1 answer
140 views

Create a select query to get employee name, total salary of employee, hobby name(comma-separated - you need to use subquery for hobby name)

see the below query table. Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby). hobby: id, name employee: id, first_name, last_name, age, mobile_number, ...
1 vote
0 answers
214 views

Which is the better way to select the last entry row from a table in PostgreSQL?

I need to test an attribute from the last entry row from a table to trigger a trigger function on PostgreSQL 9.6. Just below, there is a little piece of my table. It's doesn't have a timestamp column, ...
0 votes
0 answers
236 views

Modeling and querying a FIFO inventory table

I need some help modeling and querying a FIFO inventory table. The model I believe is quite simple and doesnt need any changes but Im not sure Ill be able to do the data transformations I need with ...
  • 23
0 votes
0 answers
38 views

How can I ORDER BY the "most relevant" entries in this context?

I have this query: SELECT * FROM "my bookkeeping" WHERE ("timestamp" > $1::date - INTERVAL '7 days' AND "timestamp" < $1::date + INTERVAL '7 days') AND (&...
0 votes
2 answers
306 views

SQL Query to remove associated records that all don't meet certain condition

I have the following tables and values in a 2014 SQL Express Server. They have been simplified to focus directly on the issue I'm having and for brevity. Tbl_Contact -ContactID Tbl_Opportunity -...
  • 3
2 votes
0 answers
672 views

How to reduce the cost of a nested loop in a PostgreSQL query

PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012. My table uniteobservation has 824 605 rows: CREATE TABLE activite.uniteobservation ( gid serial NOT NULL, numunobs character varying(20), typunobs ...
  • 177
0 votes
2 answers
1k views

Using nested json_arrayagg with two table which have many to many relationship

Consider following two tables in mysql database role(role_id); task(task_id); and a pivot table role(role_id,task_id) I can extract a single role in JSON format using the following query select ...
1 vote
0 answers
179 views

limit user's SELECT query to a fixed number of rows

i have read the following questions: Limit Postgres results for a user and also noted the recommendation of limiting a user's statement_timeout, however i would like something a little more consistent....
  • 11
0 votes
1 answer
335 views

Postgresql: most common values in array column

I have a table which looks like this: post_id tags --- ---- 1 {'tag1','tag2','tag3'} 2 {'foo','tag3', 'tag1'} 3 {'bar','tag3','anothertag'} ... Tags is an array column....
0 votes
1 answer
419 views

update datatype field int to decimal128 in mongo db in nested json structure

Hi have tried to updated nested field datatype db.test.find().forEach(function(data){ data.age = NumberDecimal(data.age); data.phone.$.mob = NumberDecimal(data.phone.$.mob); db.test.updateOne({_id:...
0 votes
0 answers
942 views

How to retrieve min/max values and corresponding dates in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data: CREATE ...
0 votes
1 answer
164 views

How to find last value from Ms Sql

I have an example table like this: ID UserId Name 1 00009 Jomy 2 00012 Tomy 3 00012 Tomy 4 00012 Tomy 5 00009 Jomy I want to get the last value of UserId = 00012, (UserId from ID number 4). I ...
0 votes
0 answers
41 views

Mongodump with 2 queries

I have two queries: var idsList = db.getCollection("collectionA").aggregate([{ $match: { createDate: { $gte: new Date("2022-01-01T00:00:00"), $lt: new Date("2022-...
-1 votes
1 answer
250 views

How to query likes and user details for posts?

I'm pretty new to the world of databases and SQL. I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
  • 101
4 votes
2 answers
9k views

How to retrieve the maximum value and its corresponding date in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data CREATE TABLE ...
0 votes
1 answer
356 views

Creating a parent table from child relationships

I have a child relationship table as CREATE TABLE ItemRelationships ( Item1 int(11) unsigned NOT NULL, Item2 int(11) unsigned NOT NULL, INDEX(Item2), FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON ...
  • 4,301
0 votes
1 answer
690 views

Postgresql: ILIKE query with % as search term

I have a JDBC query that performs an ILIKE operation. select project_name from project_table where project_name ILIKE '%test%'; The above returns all the results matching (wildcarded) the term test. ...
  • 57
-2 votes
1 answer
509 views

Named parameters in queries for SQL Server

ALL, I'm trying to find a proper syntax to do the following: SELECT a, b, c FROM foo WHERE foo.id = :id; (the query above is written as in SQLite). The "normal" query would be written as: ...
  • 217
-3 votes
1 answer
686 views

Select records based on priority

I have data stored as: TestId | Priority | --------+-----+----- test103 | 2 | test104 | 2 | test105 | 1 | test106 | 0 | test107 | 1 | test108 | 2 | ...
user avatar
0 votes
0 answers
30 views

Faster JOIN using LIKE

I have a simple tag table as CREATE TABLE Tags ( TagID int(11) unsigned NOT NULL AUTO_INCREMENT, Tag varchar(255), ParentID int(11) unsigned, Parent enum('Yes', 'No'), UNIQUE INDEX(Tag), INDEX(...
  • 4,301
2 votes
2 answers
1k views

How can I grant permission to multiple databases / tables to a user in a single query in MySQL?

I am trying the query - GRANT SELECT ON company.*, school.*, onlineshop.* TO user1@localhost; To which I am getting a syntax error. I can grant permissions one by one using - GRANT SELECT ON company.*...

1 2 3
4
5
29