in-clause

Subquery in an IN() clause causing error

hi, I'm on SQL Server 2005 and I am getting an error which I am pretty sure should not be getting. Msg 512, Level 16, State 1, Procedure spGetSavedSearchesByAdminUser, Line 8 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I am fol...

Combine 'like' and 'in' in a SqlServer Reporting Services query?

The following doesn't work, but something like this is what I'm looking for. select * from Products where Description like (@SearchedDescription + %) SSRS uses the @ operator in-front of a parameter to simulate an 'in', and I'm not finding a way to match up a string to a list of strings. Help! ...

How to put more than 1000 values into an Oracle IN clause

Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause? I have a comma delimited list of many of IDs that I want to use in an IN clause, Sometimes this list can exceed 1000 items, at which point Oracle throws an error. The query is similar to this... select * from table1 where ID in (1,2,3,4,...,...

SELECT * FROM table WHERE x IN (...a few hundred ints...)

Table has about 8 million rows. There is a non-unique index for X. Showing indexes, it shows that in the table there is a non-unique index on key name X with "seq_in_index" of 1, collation A, cardinality 7850780, sub_part NULL, packed NULL, index_type BTREE. Still, this query can take 5 seconds to run. The list of ints comes from anoth...

MySQL number of items within "in clause"

I have three tables to define users: USER: user_id (int), username (varchar) USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar) USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar) I'd like to create a middle tier user that has certain access to other users within the application. To d...

Optimizing a MySQL query with a large IN() clause or join on derived table

Let's say I need to query the associates of a corporation. I have a table, "transactions", which contains data on every transaction made. CREATE TABLE `transactions` ( `transactionID` int(11) unsigned NOT NULL, `orderID` int(11) unsigned NOT NULL, `customerID` int(11) unsigned NOT NULL, `employeeID` int(11) unsigned NOT NULL, ...

IN Operator in where clause

List<HelprClass.Organizer> org = ( from EventOrg in cntx.EventOrganizer from MstrOrg in cntx.Organizer where EventOrg.OrganizerID == MstrOrg.OrganizerID Select new HelprClass.Organizer { OrganizerName = MstrOrg.OrganizerName }).ToList() This work fine now i want to use IN Opeartor in the above Query. in t...

IN clause on multiple columns using AND , JOIN

Which of the following queries are correct SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME WHERE STATUS IN('OPEN','UPDATED') AND ITEM_TYPE IN ('ITEM1','ITEM2') AND CREATED_TIME BETWEEN 'XX' AND 'YY' AND UPDATED_TIME BETWEEN 'XX' AND 'ZZ' SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME WHERE STA...

Parameterizing a SQL IN clause on an integer column?

Jeff Atwood asked the original question about parameterizing a SQL IN clause, but I want to do this with an integer column. If I try the code from the original post I get the following exception, which makes sense: Conversion failed when converting the varchar value '%|' to data type int. Anyone try this before? ...

SQL:Casting a String to IDS with IN clause

DECLARE @STR_IDS VARCHAR(15) SET @STR_IDS='7,15,18' UPDATE TBL_USERS WHERE ID IN @STR_IDS I know the update statement would not work as the ID is of type INT and i am replacing a varachar value there .How can i change the query so that it will be executed like this in effect ? UPDATE TBL_USERS WHERE ID IN (7,15,18) Thanks in advac...

What is the best approach using JDBC for parameterizing an IN clause?

Say that I have a query of the form SELECT * FROM MYTABLE WHERE MYCOL in (?) And I want to parameterize the arguments to in. Is there a straightforward way to do this in Java with JDBC, in a way that could work on multiple databases without modifying the SQL itself? The closest question I've found had to do with C#, I'm wondering i...

SQL Server - In clause with a declared variable

Let say I got the following : DECLARE @ExcludedList VARCHAR(MAX) SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22' SELECT * FROM A WHERE Id NOT IN (@ExcludedList) Error : Conversion failed when converting the varchar value ', ' to data type int. I understand why the error is there but I don't know how to solve it... ...

MySQL specifying exact order with WHERE `id` IN (...)

Is there an easy way to order MySQL results respectively by WHERE id IN (...) clause? Example: SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3) to return Article with id = 4 Article with id = 2 Article with id = 5 Article with id = 9 Article with id = 3 and also SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3...

Mysql optimization for select query with IN() clause inside where clause (explain output given)

I have this query:- SELECT SUM(DISTINCT( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398, 2436, 2940, 1973, 2791, 1389 ) )) AS key_1_total_matches, IF(( od.id_od > 0 ), COUNT(DISTINCT( od.id_od )), 0) AS tutor_popularity, td.*, u.* FROM tutor_deta...

Problem updating table using IN clause with huge list of ids

Hi I am having a problem when trying to update a table using an IN clause, I have a big list of clients that should be updated 4500+. Update table set columnA = 'value' where ID in ( biglistofids ) //biglistofids > 4500 ids I am getting this error "String or binary data would be truncated." I tried the same script with fewer ids le...

SQL in clause with dummy rows

Hi, I'd really like know if it's possible to do a select statement, which returns exactly same records, that we put into in clause? Sample: select * from table where table_id in (1, 2, 3, 666); This table for an example has only id-s from 1 to 100, so this select will return only three rows. What I need to do, to get also one (proba...

SQL Server 2008 Update Query with Join and Where clause in joined table

Hi All, not sure why this is not working.. UPDATE ust SET ust.isUnsubscribedFromSystemEmails = 1 FROM UserSetting AS ust INNER JOIN [User] ON ust.userID = [User].userID AND [User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses) In plain English, I am trying to set the isUnsubscribed fiel...

Android 2.2 (Froyo) SQLite missing some IN clause functionality?

Recently I updated my Nexus One to Froyo (2.2) and I've noticed some significantly different behavior with SQLite. For example, I had been using a subquery (returning string data) as part of an IN clause, and the subquery portion no longer appears to function correctly. I've attached an example SQL query working with the contacts databa...

Using an IN clause in Vb.net to save something to the database using SQL

Hello, I have a textbox and a button on a form. I wish to run a query (in Vb.Net) that will produce a query with the IN Values. Below is an example of my code myConnection = New SqlConnection("Data Source=sqldb\;Initial Catalog=Rec;Integrated Security=True") myConnection.Open() myCommand = New SqlCommand("UPDATE dbo.Recordings SET S...