Sql: Return "True" If List of Records Exists

SQL: Return true if list of records exists?

Given your updated question, these are the simplest forms:

If ProductID is unique you want

SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)

and then check that result against 3, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).

If ProductID is not unique it is

SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)

When the question was thought to require returning rows when all ProductIds are present and none otherwise:

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)

or

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)

if you actually intend to do something with the results. Otherwise the simple SELECT 1 WHERE (SELECT ...)=3 will do as other answers have stated or implied.

SQL: How to properly check if a record exists

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

Check if row exists in db, and return boolean

Simple queries are better:

SELECT 1 FROM currency WHERE name=%s LIMIT 1

This will return a single row of 1 if it exists, an 0 rows if it doesn't exist.

Also use prepared statement to avoid SQL injection.

SQL - How to return Boolean value if values exists in table

I would recommend exists with a table of values. This will prevent duplicates in the result set:

SELECT v.parcel,
(CASE WHEN EXISTS (SELECT 1
FROM Property p
WHERE p.taxyear = YEAR(GETDATE()) - 1 AND
p.parcel = v.parcel
)
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END) AS ExistsStatus
FROM (VALUES ('1719309002000'),
('1024247013000'),
('1024247008000'),
('1024247001000')
) v(parcel)

Return Boolean Value on SQL Select Statement

What you have there will return no row at all if the user doesn't exist. Here's what you need:

SELECT CASE WHEN EXISTS (
SELECT *
FROM [User]
WHERE UserID = 20070022
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END

SQL: return true/false if multiple values exists or not in a subquery

You can do it by grouping by userid and a condition in the HAVING clause:

select userid
from tablename
where orderid in (2,3,4,5)
group by userid
having count(distinct orderid) = 4

This will select all the userids for which there exist the orderids 2, 3, 4 and 5.

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.



Related Topics



Leave a reply



Submit