Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

A core SQL statement, SELECT retrieves data from one or more tables or other sources of row set data such as views or table-valued functions.

0
votes
1answer
26 views

mysql update based on select result, How combine?

Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them. Select SELECT * FROM test WHERE `id`='1' Update UPDATE test SET `select_count`=...
1
vote
0answers
13 views

MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

Let's assume a data model like this: Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this ...
1
vote
3answers
42 views

Show rows with non-unique value in one field

I'm familiar with how to aggregate rows as shown in this answer: Show only duplicate values I'm also familiar with how to filter the aggregated results using a HAVING clause. What I can't seem to ...
0
votes
1answer
22 views

SSRS - select where parameter and fixed value

So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement. So what I'm trying to do is ...
0
votes
1answer
47 views

JOIN and GROUP BY with additional columns

If I run the following query on my database I get the results pasted below: SELECT t.id as ID, j.text as Text, l.location as Location, FROM [text] t WITH (nolock) INNER JOIN Text_Location tl WITH (...
2
votes
1answer
50 views

Select where number range does not overlap

I have two tables that contain records about road construction activites: table_a is the master list. table_b is a legacy list. For each road, in each year, I want to select the records from table_b ...
-1
votes
1answer
42 views

how to select query to inner join same column twice?

Table A: Col1 [TicketSubject] Col2 [Createuserid] Col3 [Modifyuserid] Table B: Col1 [ID] Col2 [Name] How I imagine it would work SELECT a.ticketsubject ,a.createuserid ,a.modifyuserid ...
0
votes
3answers
29 views

Difference of two columns with possible NULL cells

I need to sort a table by the difference of two columns as SELECT (first - second) AS dif FROM table1 ORDER BY dif DESC but the two columns can be NULL. In this case, it returns the error: ...
2
votes
2answers
60 views

Error 1242: Sub query returns more than 1 row and problem with repeating an if statement for all rows

So I've just started learning MySQL with a few exercises and I'm currently stuck at these 2 problems. Since this is technically an exercise I'd really appreciate hints instead of full solutions. Here ...
2
votes
3answers
71 views

Select the greatest value from each column, even if one of the values is null

I have a road_age_test table: create table road_age_test ( surface_year int, base_year int ); insert into road_age_test (surface_year, base_year) values (10,20); insert into road_age_test (...
0
votes
0answers
31 views

sql server - xQuery Select for each element that exists

I have a bit of trouble querying XML columns. This is part of the query I'm trying to use SELECT pof.pofdata.value('InformacionActo[1]/Vehiculos[1]/Vehiculo[1]/@Marca', 'varchar(max)') AS 'marca', ...
2
votes
2answers
43 views

Difference in query execution plan between MySQL 5.5.54 and mariadb 5.5.56

I am executing a SELECT on 2 Hosts, "old db" running mysql 5.5.54 and "new db" running mariadb 5.5.56. Both the runtime and the EXPLAIN show a difference, with the mariadb being significantly slower. ...
1
vote
0answers
30 views

How to apply a condition on “count over partition”?

I'm using Oracle 11g. Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit. With this table: datetime_id | user_id | place_id and I want to ...
0
votes
1answer
19 views

Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header?

I am using: SQLite version 3.21.0 I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot. Though, now I am wondering if ...
1
vote
1answer
15 views

Mysql query for get repeated customers list

I have a two tables oc_order and oc_customer. I want to get customers who order more than once. This is sample: oc_order: order_id | cus_id | cus_name ------------------------------- 1 | 1 ...
0
votes
2answers
39 views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?
0
votes
3answers
45 views

How to select two fields from a table using join with another table

I have a books database. CREATE TABLE `books` ( `book_id` int(11) NOT NULL, `book_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `book_subject` int(11) DEFAULT NULL COMMENT 'What genre ...
0
votes
3answers
96 views

SQL Count function returning inaccurate results

I am trying to write a query in oracle to list the number of records contained in a single table filtered by multiple columns in the tablespace. Here is the query syntax I have come up with: SELECT ...
0
votes
1answer
37 views

How to select recursively in a child parent design situation (in MySQL)?

Let's take into consideration the following tables: CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) ...
3
votes
2answers
40 views

Is there any risk in SELECT … FOR UPDATE outside transaction scope in MySQL?

In a project where I work the sql for selecting objects does always select for update, whether the context is a transaction or not. My question is if it has any risk, or performance issue, or chance ...
4
votes
5answers
90 views

Select same records that only have different values for a particular column

Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, ...
0
votes
1answer
41 views

Is it ever useful to SELECT DISTINCT on the primary key column(s)?

From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?
0
votes
1answer
34 views

Indexes for select performance

I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` enum('new','paid') NOT NULL DEFAULT 'new', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `...
0
votes
1answer
26 views

How to left join intermediate result table after filter ta(subquery)?

+---------+----------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
0
votes
0answers
20 views

Finding rows affected by deadlock on page locks [duplicate]

We've got a db that has been pretty much deadlock free for over 6 months, but a day or two ago we had over 60, all on the same two or three pages of the same table (~12,000,000 rows, ~250 bytes per ...
1
vote
2answers
42 views

MYSQL: Values of a column where two other columns have same value

I have a table say table1 with three columns lets name them col1 col2 and col3. I have a suspicion that col2 and col3 have some values that are identical i.e. to say there are some rows where col2 = ...
1
vote
2answers
27 views

Not able to make a query that give me the result that I want in MySQL with 4 tables junction [closed]

I need help to adjust my query to be able to have the result show in bottom. This is my DB illustration: And this is the command CREATE TABLE for each table: CREATE TABLE `produits` ( `sku` ...
0
votes
2answers
44 views

Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might ...
0
votes
1answer
20 views

MySQL query with inline selects is very slow

I created a long MySQL query with inline selects. SELECT users.*, (SELECT plan_payments.month FROM user_plans LEFT JOIN plan_payments ON (plan_payments.id = (SELECT id FROM plan_payments WHERE ...
-1
votes
2answers
69 views

How to SELECT value Where value = ''another SELECT" [closed]

First of all I searched for correct answer but I didn't find. I have a function: SELECT user_email FROM `registracija_complete` WHERE user_id = (SELECT `id`  FROM `base`  ...
0
votes
3answers
52 views

Calculate percent of roads (by length) where condition is >=15

I have a road condition table: CONDITION LENGTH 2 5 4 10 6 15 8 20 10 25 12 30 14 ...
0
votes
1answer
28 views

How to select two columns from two different tables using foreign keys from another third table?

I am using MYSQL and here is the schema that I have: Here is my first table user +-----+------------------+ | id | name | +-----+------------------+ | 101 | Daniel Radcliffe | | 102 | ...
1
vote
1answer
23 views

SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records

The following was one of the practice questions in the online course i took on udemy for SQL. I have a table 'emp' and consider the below names in the query map to their corresponding columns in ...
-2
votes
2answers
84 views

How to extract data between yesterday and today?

I have a column called createdate of datetime datatype. I want to extract data between today and yesterday. select * from table1(nolock) where createdate ='2018-06-01' --not getting any output I ...
0
votes
1answer
44 views

How to generate and insert primary key when using SELECT DISTINCT in OracleDB?

How do I insert (or generate and insert) a primary key when I use the SELECT DISTINCT query? If I have a table made like this: CREATE TABLE testUser( username VARCHAR2(50 CHAR), email ...
0
votes
1answer
36 views

How to get Column Names list if row IS NOT NULL in PostgreSQL

can i get 'colNew_name' from a select query like table below? original table: category_name | col1_name | col2_name | :------------ | :--------- | :----------| postgres | 1 | null ...
0
votes
1answer
24 views

Latest known road condition

Table: I have a road_condition_test table: create table road_condition_test ( road_id int, age int, condition number(10,1) ); insert into road_condition_test (road_id,age,condition) ...
1
vote
2answers
27 views

MySQL query comparing table against itself is not utilizing index

I have a table (civicrm_contact) with the following (relevant) columns: +--------------------------------+------------------+------+-----+-------------------+-----------------------------+ | Field ...
6
votes
2answers
55 views

SQL Query question (items not having some value in a column)

Here is a conundrum I need to solve with some T-SQL Magic, I tried and failed. It's one of these things that, before I think of the actual solution I think I can solve in 3 minutes with some T-SQL, ...
2
votes
5answers
112 views

Exclude composite key from select

I have 2 identical DBs, one of the tables has C1, C2, C3 , composite key. I'm trying to get the rows from B that are not present in DB A. I have tried many approaches, but at this time I even can't ...
1
vote
2answers
36 views

merge 3 mysql tables into one with no duplicates

I would like to merge 3 tables data, each have a number column in common, but all the other data differs. What I would like to achieve is selecting all of the data from the common column and merging ...
1
vote
3answers
104 views

SELECTing data based on a specific time frame

I'm using PostgreSQL. I have a table which stores data related to some orders. It has four to five columns, including: cust_code char varying (20), sugg_order integer, ordered_qty integer, date date ...
1
vote
1answer
36 views

Display single record for attributes values joining 3 tables in SQL Server?

I have 3 tables Table1 (a view) contains the record ID, Person's Name, Attribute ID and a new record for all attributes entered into the database pertaining to the person. Table2 is a descriptive ...
1
vote
1answer
142 views

Multiple rows into json

Looks my question is unusual because I didn't find the answer at all. Let's imagine, I have table A with columns: language and uri. language | uri ---------|----------------- ru | ...
1
vote
2answers
170 views

UPDATE in combination with nested SELECT queries and LEFT OUTER JOIN

I have a problem with an UPDATE statement that contains a nested SELECT statement in combination with LEFT OUTER JOIN statements. Situation: In our database there is a new column added to all people ...
1
vote
1answer
60 views

Firebird, select gives token unknown

I have a Firebird database with a table foo that have a field called WHEN. Is there a way to perform a query using the field name in SELECT? This works: SELECT * FROM foo; This not: SELECT WHEN ...
2
votes
2answers
133 views

MySQL - UUID/created_at cursor based pagination?

For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
2
votes
2answers
204 views

MySQL - Left Join takes too long, how to optimize query?

A leader may have many followers. A notification_followers table gets a single notification when a leader adds a post with an entry leader_id 1 and notifiable_id 0 (id 1,2 in table). The same table ...
1
vote
3answers
118 views

MySQL - Select when last entry before timestamp has specific value

I can find rows in a table where the last row has a specific value. However, I'm having a hard time finding them when I need to limit the max, or min, timestamp. Let's say I have table 'people'. id ...
0
votes
1answer
23 views

SQL tracking number of Selects of a databases table row [closed]

Hello I'm trying to create a way in which I could track the number of times a certain column in a database table is selected. Is there something I could implement like a trigger that could increment a ...