Because of using Hibernate, I cannot write a JPA query using exists like this (I translated it to pure SQL query): So I have to write an alternative query to check existance of activated accounts. After reading several suggestions on this website, I see a replacement: My question is: Is this bad for performan…
Tag: exists
Getting an error using EXISTS operator but correct result with IN operator in
I am getting an error An expression of non-Boolean type specified in a context where a condition is expected while using EXISTS But when I am using IN operator it is giving the correct result. Below is enter image description hereboth query Answer EXISTS is a unary operator, not a binary operator, and takes o…
Why did the ‘NOT IN’ work but not the ‘NOT EXISTS’?
I’ve been trying to improve my SQL and was playing around with a ‘NOT EXISTS’ function. I needed to find the names of salespeople who did not have any sales to company ‘RED’. I tried this and it did not work: This query ran but returned a NULL. Then I changed it to this and it wo…
SQL – How to find if the combination of column has occured before or not?
Following example demonstrates the question id location dt 1 India 2020-01-01 2 Usa 2020-02-01 1 Usa 2020-03-01 3 China 2020-04-01 1 India 2020-05-01 2 France 2020-06-01 1 India 2020-07-01 2 Usa 2020-08-01 This table is sorted by date. I want to create another column, which would tell if the id has been to th…
SQL Server : fastest way to change rows to columns
I’ve got an issue regarding two tables in my database that have the following columns: COMMENT table Composite key made up of (KEY, TYPE, NUMBER) RESULTS table An example of the COMMENT table would be this: For each KEY, there are only 3 different types A,B,C and one TYPE may have up to 0020 in the NUMB…
SQL – using ‘HAVING’ with ‘EXISTS’ without using ‘GROUP BY’
Using ‘HAVING’ without ‘GROUP BY’ is not allowed: Column ‘products.UnitPrice’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. But when placing the same code under ‘EXISTS’ – no problems: C…
SQL – WHERE NOT EXISTS [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 1 year ago. Improve this question I have a table with employees ID NAME SALARY and I am getting the employee with the most sala…
How to use WHERE EXISTS in a SQL subquery?
I need to find the distinct drinkers who have ordered drinks at a pub on VICTORIA AVE. in March 2020. Here are the tables I am trying to retrieve data from: Here is my SELECT statement so far: Answer The subquery needs to be correlated to the main query in its WHERE clause.
find missing records in corresponding tables
Let’s say I have two temp tables with data that looks like this… Here’s what I am trying to achieve 1) Exclude all records from #TEMP1 and #TEMP2 that have identical match on IdNO, CdId, ApptDate, PROC=Y I’m only interesting in finding these scenarios: 1) Find record from #Temp1 or #Te…
Oracle filtering with multiple Exists but only one can evaluate
Please consider this pseudo-query: Consider that: “From A” is much more complex and I don’t want to do more joins there. I’m filtering results and foreach record A i just want to return a single row, that’s why I have the exists in the order i want them to evaluate; How can I avo…