Questions tagged [where]
The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.
154
questions
0
votes
0
answers
31
views
Using renamed Column in WHERE clause with Comparison Operator
Question: show all jobs where max salary stays under 10000 even with a 25% increase.
This is my first week learning SQL so I don’t know very much yet but this is my statement:
SELECT job_title, ...
0
votes
1
answer
29
views
Why my simple searching query is too slow?
I don't know why searching in my "cities" table is so slow.
My query looking for a table "cities" located about 25km from the city.
I use this simple query and the database takes ...
- 73
0
votes
2
answers
58
views
Filter by field, and if zero results then filter by other field
How should a WHERE clause be constructed, such that:
It filters by a certain field
If there are zero results for filtering by the aforementioned field, then it filters by another field?
For example, ...
- 331
0
votes
1
answer
46
views
Sum of Total per Client
I want to know the total of each product that each customer has bought and I don't know how to add the total per customer.
The tables are as follows:
CREATE TABLE CLIENTES -- Customers
(
CODIGO ...
2
votes
2
answers
438
views
Why does a MySQL 'where' clause with Index make a SUM query slower?
I have a table with 5 million records. Table columns are id, customer_id, status, and amount. When I run a query without Index(status),
SELECT SUM(amount) FROM `game_results` WHERE `status` = 1
the ...
- 23
0
votes
1
answer
26
views
Return all archive records when any one record meets criteria
I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September).
The Archive Table
SELECT PTA.PlnndTrnsfrID, PTA....
- 11
1
vote
2
answers
54
views
SQL having clause
I was studying about the having clause when I came across this website.
The last two lines of this article have made me puzzled.
Similar to the WHERE clause, the HAVING clause requires that the ...
- 113
-2
votes
1
answer
32
views
Having trouble with where subquery count
Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question:
SQL query to get the ...
0
votes
2
answers
72
views
What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?
I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like:
WHERE varcharColumn <> ''
and varcharColumn2 = '2'
and ...
- 45
-4
votes
1
answer
47
views
How to join two tables in sql server? [closed]
I need some help i have a query, struggling to use join and database is sql server. Below is my table definition.
// sql server query
SELECT TOP 1000 [Station Code]
,[Transaction Code]
...
- 95
0
votes
1
answer
51
views
Given a table with date_time column, how to run a query using only the date
I have a DB with a table which has a column datetime Date_Time.
The format of this is for example: 2021-09-01 13:15:16.
I would like to set a query that is indipendent of the time but just consider ...
- 1
0
votes
0
answers
73
views
Creating a report with multiple sub-reports, grouped on one field - Microsoft Access 365
I am trying to create an MS Access Report (Microsoft Office 365) with more than 1 subreport, to be grouped under a field called "City".
The report is based on a linked table and the sub-...
- 1
0
votes
2
answers
127
views
Is it possible to nest CASE-statement into WHERE clause?
I am developing an application with FastAPI. It contains a function that brings two variables into a SQL query using the sqlalchemy.sql.text as shown here.
SELECT ATTR3
FROM TABLE
WHERE ATTR1 = ...
- 167
0
votes
1
answer
36
views
Adding new user info to current old user accounts
Hey all so I have a database that has been up and running for a few months now. I had a request to add the users First and Last name as well as their organization.
This was easy enough to do for NEW ...
- 105
0
votes
0
answers
47
views
How does MySQL handle indexes and where clauses with joins
I am querying large tables with joins and where conditions.
An example query:
SELECT *
FROM forename
JOIN forenameLength ON forenameLength.nameRef = forename.nameRef
WHERE forename.placeRef = 18000 ...
- 155
0
votes
2
answers
53
views
Complex `where` dramatically affects query performance
There are much more complex queries running on these tables that are much faster but this one takes 90+ seconds to run.
What am I doing wrong here? How can this be improved?
SELECT
r.id ...
1
vote
0
answers
46
views
PostgreSQL - Dynamic filter expression
Given a simple function (SQL for the sake of example, can be plpgsql)
CREATE OR REPLACE FUNCTION dynamic_filter(
f INT[] DEFAULT NULL
) RETURN INT[] LANGUAGE SQL AS
$$
SELECT ARRAY_AGG(val)
...
- 183
1
vote
2
answers
47
views
Under what circumstances would these 3 queries return different number of results?
Hello I have the following question:
Query 1:
SELECT b.id,
(
SELECT SUM(cb.invoice_amount) AS sum
FROM payment_lines cb
WHERE cb.batch_id = b.id
) AS batch_total_invoice_amount
...
- 19
0
votes
1
answer
52
views
Subquery with variable in where
I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null.
But why not do ...
0
votes
2
answers
24
views
Update field with the longest varchar field
I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query:
SELECT TOP(1) options
FROM ...
- 103
0
votes
2
answers
4k
views
Unknown column in 'where clause' when using an alias for a subquery in MySQL
This simple query:
select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
where n_dataset = 0
returns the following error:
ERROR 1054 (42S22) at ...
- 103
0
votes
1
answer
250
views
Is there any better way to list all the databases in Postgres except database named postgres?
SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')
Using the code above, I get all the databases except postgres as shown in the image ...
- 117
0
votes
1
answer
112
views
Materialized view logs — with condition?
I've created materialized view logs on a WORKORDER table in an Oracle 19c database.
create materialized view log on my_workorder_system.workorder with rowid;
The plan is to integrate the WORKORDER ...
- 1,373
2
votes
1
answer
47
views
Check the result of a math expression in mysql
Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.
a b oper c
-------------
2 3 + 5
4 2 / 3
...
- 123
2
votes
2
answers
56
views
Where Clause for Results Appearing after Occurence
I'm wondering if it's possible to find records that only appear after the first occurrence of a where clause. I'm currently using PostgreSQL for this example but something not PostgreSQL specific ...
- 207
0
votes
2
answers
29
views
(Beginner) MYSQL: What can I do about this where statement?
Suppose I have some table named tableA and column1 in tableA has some null values and strings.
Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column ...
- 3
-1
votes
1
answer
189
views
Need to filter by month
I am using SQL Server Management Studio.
I am trying to filter by month using dtm DATETIME as my format XX/XX/XXXX. I am very new to SQL, so I was trying to just filter by the month of January 01 with ...
- 11
2
votes
1
answer
759
views
Does using inequality (<>) or NOT in where clause lead to SQL ignoring the index?
I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index.
https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-...
- 31
1
vote
1
answer
162
views
Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)
I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views.
My version required the View to be refactored into a Function as I ...
1
vote
1
answer
43
views
CASE in WHERE clause - retuns a single-row result instead of 4 rows?
Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
- 151
0
votes
1
answer
18
views
Inconsistent ResultSet from Procedure with Variables
I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.
In this question, there are in actual fact two questions. Number 1 ...
- 111
0
votes
1
answer
243
views
How does adding LIMIT work with Where conditions in mysql?
In my database, there is only one record which satisfies the condition iv_title = 'ball skills'.
Now I'm executing the following query :
SELECT
*
FROM
`imageAndVideo`
WHERE
`iv_title` = '...
- 105
5
votes
2
answers
122
views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
- 51
2
votes
1
answer
2k
views
How to check other table for value during insert
In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?
I have attempted to use WHERE NOT EXISTS as I want to not ...
- 123
0
votes
1
answer
47
views
How to use WHERE condition when using ON clause [closed]
I have looked at similar posts and have tried them but dint seem to work. In the below query on a MSSQL 2015 database, I need to filter out inactive users. That is, only return results where 'Active' =...
- 3
0
votes
1
answer
21
views
Performance of query from this schema
Essentially all comments are stored in this schema (dbml)
table comments {
commentID BINARY(16) [not null] //UUID
post_date timestamp
postID BINARY(16) [not null] //UUID
has_reply bool [...
- 3
0
votes
0
answers
43
views
Speed up query on large MySQL table
SELECT property, geo, stars
FROM table
WHERE SUBSTRING_INDEX(geo, '>', 3) LIKE '%$mylocation%'
...loop to show results
geo column has values like - Spain>Canary Islands>Lanzarote>Some ...
-1
votes
2
answers
29
views
UPDATE with too much values at IN state takes too long
I have the next query:
UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN
(23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
2
votes
2
answers
3k
views
Perform TABLESAMPLE with WHERE clause in PostgreSQL
I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.
This runs fine:
select * from customers tablesample bernoulli (1);
But I cannot figure out how ...
- 129
0
votes
2
answers
157
views
How to select Overdue Rows with Date Frequencies?
+------------------------+--------+
| Invoice_id | due_date | amount |
+-------------+----------+--------+
| 20 |2020-01-18| 1250 |
+-------------+----------+--------+
| 21 |2020-...
- 101
-2
votes
1
answer
22
views
Can't manage to work with comparing days
In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed.
I found some answers on the Internet but they all are at least 5 years ago with ...
- 101
-1
votes
2
answers
185
views
Select Rows From One Table, Where Matching Rows in Another Table Has Empty Column
I have multiple Postgres tables that look like the following. They are generated on a daily basis, so each just represents the data that was processed for that day:
data_20200113
+-----------+-------...
- 1
0
votes
1
answer
421
views
bridge table joined table have both the keys equal to both the keys
SELECT DISTINCT i0.id,
i0.name
FROM main_table AS i0
RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id"
RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" ...
- 177
0
votes
1
answer
26
views
Time interval is returning all records or no records
I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial
After running the query at 18:30 UTC, my query looks as follows:
RETURN ...
- 69
1
vote
1
answer
43
views
Null check condition in Where clause
DECLARE @SubDesc1 nvarchar(100)
SET @SubDesc1 = null
I would like to do something like that.
Query 1
SELECT *
From Table1
where Category = 'Category1'
AND [Description] ='ABC'
AND ...
- 13
0
votes
1
answer
19
views
Does range improve match operation and which runs first, range or match
I am using MySQL version 5.6.10
I have a very very large table large_table and a relatively small table small_table
The small_table has only the records of the date 2019-11-20
There is a column I ...
- 193
0
votes
1
answer
76
views
select subquery condition in where statement
I am getting this error:
ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier
...for the following query:
select PARTY_ID,
PARTY_NAME,
IT_ID,
IT_ID ||...
- 11
0
votes
3
answers
81
views
How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons?
I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.
I'm using SQL Server and my code looks like this:
SELECT history.date,
...
- 33
0
votes
1
answer
2k
views
How to use comma separated columns in CASE WHEN Statement?
I am trying to add below CASE WHEN Statement in SQL WHERE Clause, But SQL Server not allowing me to do so. All column used are of same Data Types but still its giving me error, Can anyone please check ...
- 147
1
vote
1
answer
152
views
WHERE IN subquery too slow
I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k ...
- 11