Questions tagged [where]
The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.
100
questions
0
votes
0answers
27 views
Sql, get data/rows for more selected ids in one condition, from file
I am using PostgreSQL 11.5 on Ubuntu
Question
How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids.
Example
|product_id |product_name ...
1
vote
1answer
33 views
Optimization on table selected where primary key
I have this query:
SELECT *
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'))
AND (wp_dev_posts.ID = ...
-1
votes
2answers
29 views
How to get column values in where condition in sub query? [closed]
I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query.
SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as ...
0
votes
0answers
24 views
How to get the data using where_in (1,3 ) table in mysql?
We have table
Table 1 is User
Table 1:
id | name | sub_code
-----------------------------
1 | name1 | 1
2 | name2 | 1,3
3 | name3 | 2,4
Table 2 is ...
1
vote
2answers
84 views
IN clause not working on mysql 8.0.17
I was on mysql:8.0.15 and I updated to mysql:8.0.17
On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect
On 8.0.17 the same query with same data return 0 ...
0
votes
3answers
22 views
Order of WHERE IN result for PostgreSQL
If I use the following selection
SELECT * FROM users WHERE id IN (3,1,2)
Can I expect the result to be in the same order like the provided ids?
0
votes
0answers
23 views
How performant is finding a substring in database?
To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item.
For example:
I have a request to /animals/dog/darktheme/. The ...
1
vote
1answer
23 views
How to order by then start at first non null value and select a limit for number of values
I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to ...
3
votes
2answers
23 views
Optimizing PostgreSQL where clause with OR
I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
0
votes
0answers
21 views
Selecting time_Stamp >= time('now') GROUP BY firstName RETURNS all first names and same time?
I am trying to select two columns from two different tables, where a value exists in both tables and the time in one table is >= now - 30 minutes. How ever it is returning all the firstnames and then ...
0
votes
1answer
587 views
CASE in WHERE clause in MySQL
I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:
DELIMITER $$
CREATE PROCEDURE `case_in_where`(IN `column_selector` ...
0
votes
2answers
431 views
Mysqldump multiple tables with where clauses in single transaction
Suppose, we have 2 tables: t1,t2 in database db:
Expected the below code to dump tables based on conditions specified in the where clauses:
mysqldump --login-path=login_path \
--single-...
0
votes
1answer
13 views
Where clause no give expected result
where clause not show expected result.
declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0
and (
( @PracticeId =1000 and(I.PracticeId=@PracticeId ...
0
votes
1answer
66 views
How to search multiple values using LIKE
I'm using MySQL and I have a table with these fields :
id
name
kode_fakultas
year
I want to create a form for searching based on user input. Example, if the user search is based on name, but the ...
3
votes
1answer
1k views
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
...
0
votes
1answer
32 views
MySQL under what scenario suitable to use WHERE EXISTS
I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.
select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
from `table_2`
...
0
votes
2answers
57 views
How to efficiently handle querying for different combinations of optional parameters
Consider the following Tables:
Table: Parts
part_id INT PK
part_number VARCHAR
Table: Jobs
job_id INT PK
job_number VARCHAR
Table: Job_Parts
part_id INT FK PK
job_id INT FK ...
2
votes
1answer
44 views
where clause after limiting results
I have a question about getting results after limiting the rows.
I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after ...
1
vote
2answers
103 views
SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause
I have a MySQL query:
SELECT
p.informationProvider as pGLN,
p.productID,
p.productStatus
FROM tblproducts AS p
WHERE
-- informationProvider is of type varchar(14)
p....
0
votes
1answer
51 views
In where clause, does MySQL continue checking conditions if first condition before AND falsy
I have a question about and AND inside a where clause of a query. Within the where clause, I have a subquery but it is after a few other, row value conditions, like validated = 1 AND (subquery). I ...
0
votes
2answers
38 views
How space is compared in where condition
I've come across below query where it is column being compared the space character.
SELECT *
FROM company
WHERE emp_name > ' ' -- space char
AND emp_id = 123
AND active_flag = 'Y';
...
2
votes
1answer
56 views
Why does MySQL optimize query with “WHERE … OR” so badly?
Hey there,
this is my first question so please be patient if I miss to add something in here.
My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
0
votes
2answers
3k views
LEFT JOIN with WHERE (Microsoft Access SQL)
Given following 2 tables a, b:
table a
ID job
1 farmer
2 physician
3 bank clerk
table b
bID ID lang job
1 1 en farmer
2 2 en physician
3 3 en bank clerk
4 2 de Arzt
5 1 ...
-1
votes
1answer
49 views
Need Help With View Where Condition
I am creating a View to grab contact information from my database. This is the code I have for the view at the moment:
CREATE VIEW SupplierContact AS
SELECT
sc.first_name + ' ' + sc.last_name AS ...
0
votes
2answers
169 views
Sql Substring and Search String
I have a table in my database for storing supplier contacts and it is laid out like so:
Table: supplier_contacts
contact_id INT IDENTITY(1,1)
first_name VARCHAR(50) NOT NULL,
last_name ...
2
votes
2answers
86 views
In what direction SQL Server WHERE clause conditions are checked?
I have this query:
SELECT RCodeID as 'ListItemID',
RCode as 'ListItemName'
FROM RCode
WHERE RCode not like '%,%'
AND ISNUMERIC(RCode) = 1
AND CAST(RCode as int) >= CAST(@...
0
votes
1answer
57 views
Update + where not exist
once again, I come here for your rescue.
how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of ...
2
votes
2answers
611 views
Use of ANY function for multiple values
I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.
My SQL:
SELECT
*
FROM
company_employee_contacts
WHERE
corporate_complaint_type_ids ...
1
vote
3answers
661 views
SQL Server COUNT() seems to be ignoring WHERE [closed]
I am trying to set up a SQL Server query that lists the count of people signed up for tours during a conference event and then displays the total sum. This is my SQL code that is coming close, but it ...
0
votes
1answer
61 views
SELECT SUM and remove similar rows
SELECT
SUM(coins.available_supply * hist_all.price) AS market_cap_usd_all
FROM coins
LEFT JOIN hist_all ON coins.id = hist_all.coins_id
WHERE hist_all.price != 0
AND (hist_all.second_coin = '...
0
votes
0answers
25 views
Get Schedule for the current day or first coming up
I am building a scheduling app, where I assign schedules to certain members but those schedules can change at a certain date or be assigned for the future.
My database structure is like so:
members ...
0
votes
1answer
31 views
Problem with JOINS
I have a problem with my database or query.
I've three tables, one for videos, another for tags of the videos and other for the tags descriptions and theirs id.
I want to access to the TAG_DES.
For ...
1
vote
2answers
139 views
Postgres - How to get Multi-Count Query using a Where Clause for a Join Table
I have the following query that gets the counts across multiple tables that are joined:
select
count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
count(distinct ...
2
votes
2answers
103 views
SQL column=ALL(query): Equivalent meaning
I know that the expression:
WHERE column <> ALL(subquery)
could have been written as:
WHERE column NOT IN(subquery)
What would be the equivalent for
WHERE column = ALL(subquery)
I don’t ...
5
votes
1answer
1k views
Condition in WHERE vs condition in CONNECT BY
Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.
select concept.concept_id, concept.PARENT_ID
from ...
-1
votes
1answer
27 views
Ambiguous syntax for IN clause
I am working on some select query and I found this syntax
SELECT NOM
FROM INGENIEUR A
WHERE 7 IN (SELECT CODEPT
FROM EMPLOI
WHERE MLE = A.MLE)
I have never seen this ...
0
votes
3answers
1k views
SQL beginner, need help with WHERE syntax error [closed]
I am having a problem with a sql query that is supposed to find authors that have published at least one book priced $13 or more. IT is supposed to display first and last name, sorted by last name as ...
0
votes
1answer
51 views
How to return duplicates if IN (…) has duplicates? [closed]
I have a list containing 1, 1, 2.
I am using it in a query like this:
SELECT A.* FROM A WHERE id IN (1, 1, 2)
The output is this:
id
---
1
2
I want the results to be like this:
id
---
1
1
2
...
0
votes
1answer
50 views
How to speed up counting and filtering on table with a lot of columns and filter conditions?
I have TABLE_A in my database that has a lot of columns.
Let's say there are 100 columns: COLUMN_1, COLUMN_2, COLUMN_3, .. COLUMN_100
There are so many columns not because of denormalized design, ...
-1
votes
1answer
350 views
Why don't we prefer SubQueries instead of JOINs
For two tables 'Employees' and 'Sales', if we want to get total sales done by employee with a specific employee name.
Employees table
EmployeeID,
EmployeeFirstName,
EmployeeLastName,
...
1
vote
2answers
686 views
SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure
I have a stored procedure that creates a dynamic SQL Query. I use sp_executesql, to have it SQL injection safe.
I have a second stored procedure that generates a where logic.
For example if I run the ...
0
votes
1answer
522 views
MySQL / MariaDB : Many indexes vs. One multi-column index
I need to optimize my MariaDB tables index.
I've read a lot of very interesting things on StackExchange, but I'm not sure to have the answer for my case.
In my case, I have a table named TableA which ...
0
votes
1answer
172 views
Oracle second date range comparison ignored in where clause
I have the query below and am receiving an unexpected dataset returned back. I was wondering if you all might be able to explain what is going on for me? I am using Oracle 11g.
I understand having ...
2
votes
2answers
97 views
Select roads that are between FROM and TO
I have a road table:
create table road
(road_id int,
name_ nvarchar2(15),
from_ nvarchar2(15),
to_ nvarchar2(15),
sort int
);
insert into road (road_id,name_,from_,to_,sort)...
2
votes
1answer
56 views
Limits with IN operator [closed]
Are there any upper limits on the amount of terms that could be between the parenthesis on an IN operator?
For example, the query would look something like:
SELECT * FROM table WHERE number IN (1,2,...
1
vote
5answers
2k views
IN clause too slow
Ive been searching for about 1 hour and i have not found anything.
I have this problem:
When I do this query:
select order_id
from or_order@smartflex
where order_id in ( select distinct numeroot ...
1
vote
1answer
1k views
Using DATE part of datetime column in WHERE clause
I'm building a POS system, where I collect the date & time that the event took place. I would like to pull some reports by only using the DATE part of the datetime column in my SQL queries.
My ...
1
vote
2answers
741 views
Recommended Postgresql index type to be used with not equal (<>) where condition
I need to query two huge tables (more than milion records). Besides other conditions, there is also a not equal (<>) where condition on a columns pair of varchar type. It has to exclude one in a ...
8
votes
3answers
449 views
Locate the smallest missing element based on a specific formula
I need to be able to locate a missing element from a table with tens-of-millions of rows, and has a primary key of a BINARY(64) column (which is the input value to calculate from). These values are ...
1
vote
1answer
1k views
How does a subquery use the main query columns?
I'm really confused by this SQL here, it's used in our app but I have no idea how it works. I put it in from examples I found online and it seems to have no issues.
What it's doing is grabbing the ...
