Stack Overflow
Stack Overflow

Questions tagged [in-subquery]

Use this tag for questions related to subqueries (i.e. a query that is nested inside a SELECT, INSERT, etc.), that use IN at their outer statement.

An example of an is:

WHERE th.parent = 1015 IN (
    SELECT DISTINCT(th1.tid)
    ...
);

as demonstrated in MySQL DELETE FROM with subquery as condition.

Note that this tag is general in its scope, i.e. that it is not restricted in SQL questions only, but any question referring to this type of IN subqueries is welcome to use this tag.

128 questions
3
votes
1 answer

Dapper.Net: IEnumerable parameter throws exception: No mapping exists from object type System.Int64[] to a known managed provider native type

I am using Dapper.Net against SQL Server 2008 R2 in the following code to pass a List parameter to run a SQL query that has a WHERE IN clause, but I get the exception: No mapping exists from object type System.Int64[] to a known managed…
Abe
  • 6,386
  • 12
  • 46
  • 75
3
votes
4 answers

Can Someone Help me Optimize this mysql statement?

I have one table that I'm using to build groups with in my database. The table contains a list of group names and ids. I have another table that has users, and a third table showing the relationships. (userid, groupid). The situation is this, I…
SilicaGel
  • 459
  • 3
  • 11
2
votes
3 answers

Multiple values IN

Normally IN is used with one value: SELECT * FROM data WHERE f1 IN () It is possible to use it with multiple values: SELECT * FROM data WHERE f1 IN () OR f2 IN (); But can I remove duplication, something like: SELECT…
valodzka
  • 5,535
  • 4
  • 39
  • 50
2
votes
1 answer

MySQL returning multiple rows as columns

I'm a bit rusty with SQL, I have one simple table col1 col2 col3 col4 ident1 name1 data1 data3 ident2 name1 data8 data7 ident3 name1 data3 data8 ... ident1 name2 …
user869097
  • 1,362
  • 8
  • 16
2
votes
3 answers

MySQL: WHERE IN any of subqueries

How to restructure this query: SELECT * FROM tbl t WHERE ( t.id IN OR t.id IN OR t.id IN ) ... into something that looks more like the following: SELECT * FROM tbl t WHERE t.id IN ( OR…
mae
  • 14,947
  • 8
  • 32
  • 47
2
votes
2 answers

Update statement gives wrong result with subquery

I have the following query which gives no error when I used a non-existent column reference in the subquery. The column which I referred in the subquery is actually a column in the table being updated. create table tbl1 (f1 bigint, f2 char(10), f3…
Geek shadow
  • 59
  • 3
2
votes
2 answers

PostgreSQL : comparing two sets of results does not work

I have a table that contains 3 columns of ids, clothes, shoes, customers and relates them. I have a query that works fine : select clothes, shoes from table where customers = 101 (all clothes and shoes of customer 101). This returns clothes -…
codebot
  • 517
  • 8
  • 29
  • 55
2
votes
2 answers

Subquery with 2 parameters in SQL

I have a table in SQL which looks like this: [ Now, I want the resultant table based on 2 conditions: Prev_trans_id should match the transactions_ID Only those entries should come where mod of Amount value is not equal. The resultant table should…
Rahul Agarwal
  • 4,034
  • 7
  • 27
  • 51
2
votes
1 answer

Oracle subquery funniness

Imagine a query SELECT Col_A FROM TABLE_A WHERE Col_A IN (SELECT Col_A FROM TABLE_B) Except, TABLE_B does not have a Col_A column; Only TABLE_A has it. I tried this in Oracle 12 and I'm not sure how far back in versions it goes but it looks like…
user1111871
  • 137
  • 1
  • 12
2
votes
1 answer

How to implement sql subquery in mongo aggregation pipeline?

select name, age from table1 where name in (select name from table1 where age > 20) How to convert above sql subquery to mongo aggregation pipeline?
Marika
  • 31
  • 1
  • 4
2
votes
4 answers

Better tsql query to figure out who's a new customer (or alternative to NOT EXISTS)

I have an table with all our orders. The orders are linked to a person. And every person is attached to a company. Now I need a list of all companies have never ordered before a certain date. The query works fine, but it's very slow. This is my…
Remy
  • 12,555
  • 14
  • 64
  • 104
2
votes
4 answers

MySQL: Using "In" with Multiple SubQueries?

I'm trying to use this query: SELECT COUNT(PF.PageID) AS Total,P.PageID FROM Pages P LEFT JOIN Pages_Filters PF ON PF.PageID=P.PageID WHERE P.PageID IN ( (SELECT PageID FROM Pages_Filters WHERE FilterID="1" AND…
Nick
  • 10,904
  • 10
  • 49
  • 78
2
votes
2 answers

Hive - Where and OR clause error

Hi I am trying to run this query in Hive, but get the error 10249 (Unsupported query expression - only 1 subquery is supported...) select count(*) from ( select * from tableA union all select * from tableB ) a where a.field1 in (select…
dnf999
  • 33
  • 2
  • 5
2
votes
2 answers

Trying to delete from Join and I know I have funky spacing and I know I have dup IMEI's

DELETE dbo.bBoxDetail FROM dbo.bBoxDetail AS BD INNER JOIN dbo.bBoxHeader AS BH ON LTRIM(RTRIM(BD.bBoxDetailId)) = LTRIM(RTRIM(BH.bBoxId)) WHERE LTRIM(RTRIM(BD.ESNs)) = (SELECT LTRIM(RTRIM(IMEI)) FROM dbo.tmpIMEI) I get this error: Msg 512, Level…
TooMuchToLearn
  • 173
  • 1
  • 3
  • 10
2
votes
2 answers

MySQL DELETE FROM with UNION subquery by IN condition

I have tripped up on a curious SQL error. The last query doesn't work. Of course I can just split that DELETE into three queries, but I really wonder why MySQL doesn't let me do it this way. A little example: (SELECT id FROM stairs WHERE building =…
Julian Habekost
  • 262
  • 1
  • 4
  • 17
1
2
3
…
8 9