Questions tagged [in-clause]

The IN clause of a SQL query is a function, generaly used within a WHERE clause, that returns true if a value is in a given set. Use for questions relating to this clause.

The IN clause of a SQL query is a function, generaly used within a WHERE clause, that returns true if a value is in a given set. For example,

SELECT *
FROM foo
WHERE bar IN(24, 37, 82);

will only return rows from foo if bar is either 24, 37, or 82.


More examples:

SELECT foo
FROM (SELECT 27 AS FOO FROM dual)
WHERE foo IN(35, 45, 26, 98, 27)

returns 27

SELECT foo
FROM (SELECT 64 AS FOO FROM dual)
WHERE foo IN(35, 45, 26, 98, 27)

returns no data found

257 questions
0
votes
2 answers

Is it possible that LEFT JOIN fails while subquery with NOT IN clause suceeds?

A while I have posted an answer to this question PostgreSQL multiple criteria statement. Task was quite simple - select values from one table if there is no corresponding value in another table. Assuming we have tables like below: CREATE TABLE…
anon
0
votes
1 answer

how do you know which items contained in the IN clause that were not found?

I have a list with 10 items on my IN clause, in my SELECT only 8 items were found. how do you know which items contained in the IN clause were not found? SELECT * FROM SOME_TABLE WHERE ID IN (1,2,3,4,5,6,7,8,9,10) My table is : **ID NAME**…
William Miranda
  • 493
  • 1
  • 7
  • 13
0
votes
0 answers

Mysql WHERE IN (*)

I have a procedure A which takes an array of strings. By calling another procedure B, i break this array in this format: '1','2','3','4' In case there is only one value, it just displays as '1' I want to return * in case the array passed to the…
sameer
  • 220
  • 2
  • 6
  • 18
0
votes
2 answers

MySQL Unexpected Results: "IN"-clause (number, 'string') on a varchar column

In MySQL, why does the following query return '----', '0', '000', 'AK3462', 'AL11111', 'C131521', 'TEST', etc.? select varCharColumn from myTable where varCharColumn in (-1, ''); I get none of these results when I do: select varCharColumn from…
Briguy37
  • 8,342
  • 3
  • 33
  • 53
0
votes
1 answer

Count and IN Clause Issue

I have a table named as employee history and its structure is as follows. tbl_employee_history Structure id|history_nr|history_value|employee_id 1 82 83 1 2 86 84 1 3 87 85 1 4 603…
user2216168
  • 21
  • 1
  • 1
  • 4
0
votes
2 answers

Where condition not working with IN Clause

I have a query. In this query I am using a sub query to get the data from the same table with different condition and in the main query I am mentioning the ids that are used to get data in the sub query and putting a condition that the values that…
user2216168
  • 21
  • 1
  • 1
  • 4
0
votes
1 answer

not passing List of String as single parameters inside preparedStatement.setObject()

I want to pass a List of String coming from request parameter to the preparedStatement.setObject() as a single parameter.Here I have coverted list of Objects to a single String. So while passing this converted String to setObject method it is…
MankitaP
  • 57
  • 1
  • 1
  • 7
0
votes
1 answer

Using NamedParameterJdbcTemplate, how to retrieve record sets from sql server that match a list of regex patterns

I'm new to spring framework. I'm trying to retrieve record sets from a SQL server 2005 database table using NamedParameterJdbcTemplate that matches multiple patterns(patterns are stored in an ArrayList) . The below code retrieves record sets…
0
votes
2 answers

Selecting A Row Based On One Value In A Multi-Value Array Using IN Clause

I have a column in a database with values stored as a comma separated array; let's say a sample entry in the database would be: 1, 5, 8, 15 I am trying to use this data now in a SELECT statement to allow a user to select an entry in the database…
0
votes
3 answers

Using report parameters in record selection

I have a Java application the invokes Crystal Reports through its Java libraries. One of the reports has the following clause in its record selector: and ({DriverMotionView.GROUPID} = {?GroupID1Parameter} or {DriverMotionView.GROUPID} =…
Andy
  • 99
  • 10
0
votes
3 answers

Subquery in an IN() clause causing error

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…
Aamir
  • 791
  • 3
  • 15
  • 28
0
votes
2 answers

SELECT WHERE IN not retrieving all records

Edit: To clarify, I'm using PDO to MySQL database Ok I have a slight problem with a query. I've tried googling and 'stackoverflowing', to no avail. I'm trying to select all records from a table called download store, where the ownerid =…
0
votes
4 answers

PHP + convert array for use with IN statement

Lets say I want to send an email to the people who like big sized candy. When I do this: $query1 = mysql_query("SELECT id_candy FROM `candylist` WHERE candysize > 100") or die(mysql_error()); $result = mysql_fetch_array( $query1 ); $query2 =…
Khul
  • 19
  • 1
  • 1
  • 8
0
votes
3 answers

How to use more than 1000 values in IN condition?

I am using java. I have 2000 key values in object level and have to fetch the corresponding records for that values from the DB. I found temp table advice here, but won't it affect performance? How do I insert those values in a single shot, so that…
Samurai
  • 843
  • 6
  • 23
  • 44
0
votes
1 answer

MySQL query optimization with LIMIT, ORDER BY, bool clause and IN clause

I need some advice. To be more general I explain on following example: I have MySQL InnoDB table 'posts' with fields: `id` - INT UNSIGNED AUTOINCREMENT `title` - VARCHAR(100) `abstract` - TEXT #can be converted to VARCHAR(2048) `content` - TEXT…
G.N.
  • 11
  • 3