Stack Overflow
Stack Overflow

Questions tagged [where-in]

An SQL-standard condition of the form WHERE SOME_COLUMN IN (1,2,3) or using a subquery to create the list, eg WHERE SOME_COLUMN IN (SELECT X FROM MYTABLE WHERE Y)

For example:

SELECT *
FROM MY_TABLE
WHERE MY_COLUMN IN (1,2,3,5,8,13)

or commonly using a subquery to generate the list:

SELECT *
FROM MY_TABLE
WHERE MY_COLUMN IN (
    SELECT SOME_COLUMN FROM SOME_TABLE WHERE <some condition>
)

The subquery version is a source on many performance problems, because most optimizers do not optimize this properly.

Most IN (subquery) queries can be rewritten to use a join that does perform well.

497 questions
-1
votes
5 answers

How to use select with a variable

To simplify I have a table and I just want to make a select with the data at blue, is tricky for me because I must use the where textanswer='dimibilli d2' but I will only get the records 5, 7, 10, 12 but I want to get the 6, 8, 9 to How can I do…
António Silva
  • 3
  • 2
-1
votes
1 answer

Get any of the item from the Array with whereIn in Laravel 5.3

I'm using laravel 5.3 and trying to build a query with multiple where and wherein function. Here is my code: $posts_id = "1,3,4"; //from my query $results = Post::select('*'); $results->whereIn('posts_id', [$posts_id]); $resutls->get(); There…
Niamul
  • 73
  • 9
-1
votes
1 answer

Avoid Cartesian Product in SQL when using Where in clause

My query goes below select a.col1,a.col2,b.col1,b.col2 from table1 a, table2 b where a.col3=value and b.col2 in (select col from table1 where col3=val) This is giving be repeated values Eg. Result obtained ----------------------- S.No| Name|…
praveenrsmart
  • 89
  • 11
-1
votes
3 answers

using farword slash in sql where condition

I'm trying to search an element which starts with / below is the query. I've already tried... Select id,url from myindex where url='[/]%' ; Select id,url from myindex where url='/%'; but didn't work. any suggestions to fix this?
Your Friend
  • 1,119
  • 3
  • 12
  • 27
-1
votes
2 answers

Solution for subquery in MySQL WHERE IN

I am trying to solve WHERE subquery or to find different solution. What I am trying to achieve is based on this query: SELECT c.orig_point_id, (SELECT attempts FROM (SELECT orig_carrier_id, orig_point_id, …
infinite
  • 21
  • 6
-1
votes
1 answer

Why is this SQL Server stored procedure considered to have incorrect syntax (Error 102)?

Trying to create a stored procedure in LINQPad 4, I get Error 102: Incorrect syntax near 'CHOPHOUSE' I changed this line from an earlier stored procedure: where up.Unit = @Unit ...to this: where up.Unit IN ('CHOPHOUSE', 'CRAFTWORKS SC', 'GORDON…
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
-1
votes
1 answer

MySQL where-in statement should return records by order of where-in

Let's say I have following SQL statement: SELECT * FROM someTable where id in (4,5,2,3,1); This will return all records from someTable which have an id equals to 4, 5, 2, 3 or 1. The order of which they are returned is: id data ============ 1…
STheFox
  • 1,478
  • 4
  • 18
  • 24
-1
votes
2 answers

PHP SQL WHERE name IN (array...)

I have an array: $array = array ( option( 'one' ), option( 'two' ) ); output: array(2) { [0]=> string(7) "result1" [1]=> string(7) "result2" } and a sql select: SELECT * FROM xx WHERE url IN (".implode(',',$array).") ORDER BY id DESC but the…
Erfo
  • 37
  • 1
  • 8
-1
votes
3 answers

WHERE IN query?

With the next database: I need to select all soldiers who have been ONLY in battles in their own planet and I don't have any idea of how can I make it. This is what I've tried. SELECT id_soldier, name FROM soldier, battle_log, battle WHERE…
therealbigpepe
  • 1,489
  • 2
  • 16
  • 23
-1
votes
1 answer

MySQL subquery WHERE IN

I have this query below and ran it, but it kept saying I have a different version of MySQL? I think the problem is WHERE IN and its subquery. Can anyone help me? SELECT Field2, Field3, Count(Distinct Field1) FROM learning.master WHERE Field3 IN…
prodirector
  • 1
  • 1
-1
votes
2 answers

MySQL Getting last 5 entries for each id in a SELECT - WHERE IN statement

I would like to select last 5 entries for each of the id in a SELECT - WHERE IN statement. //How to get last 5 entries for each id SELECT * FROM table1 WHERE id IN (111,222,333,.....) ORDER BY date DESC LIMIT 5 EDIT: Example of how data…
Cryssie
  • 3,047
  • 10
  • 54
  • 81
-1
votes
1 answer

Update a column on rows of which another column has multiple values

I am having trouble with something like this: UPDATE `database` SET `col1` = 0 WHERE `col2` in (1,2,3,4); Following is an actual failed query. Error Message: 1064: You have an error in your SQL syntax; check the manual that corresponds to your…
markscarts
  • 11
  • 3
-1
votes
2 answers

Double WHERE IN OR in MySQL query does AND instead of OR

I have the following MySQL query as a result of a piece of laravel code. And I'm trying to figure out where the code fails by analysing the resulting query. The query should select all the twitter_statuses where there is a tag_id in…
Martijn Thomas
  • 861
  • 3
  • 13
  • 24
-1
votes
1 answer

How to write a search query in the below condition

I have a table named ad_categories and its structure is like this And i have another table named pub_categories and its structure is like this id in both table are different I need to select id's of ad_categories WHERE any of the ad_categories.cid…
Juice
  • 3,023
  • 6
  • 39
  • 66
-1
votes
2 answers

mySQL PHP WHERE IN returns a table- How do I parse this table?

How do I use the data returned by a SQL WHERE IN(x,y) statement with PHP? $sqlquery = "SELECT * FROM schedules WHERE userid IN ('35','101','45')"; if ($test_stmt=$mysqli->prepare($sqlquery)) { //Get all data in…
user2415992
  • 481
  • 7
  • 22
1 2 3
…
33 34