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
2
votes
3 answers

WHERE [1 of these values] IN [1 of these values]

I have the following query : SELECT A.id FROM logsen_alertes A WHERE ( SELECT LA2.type_colocation_id FROM logsen_liaisons_annonces_types_colocations LA2 WHERE LA2.annonce_id = 25 AND LA2.annonce_type = 4 ) IN ( SELECT L4.souhait FROM…
VaN
  • 2,180
  • 4
  • 19
  • 43
1
vote
2 answers

MySQL : interval around id column and return another one from subquery with multiple columns

I would like to run a query from a table where the content is like that : id | col1 | col2 | col3 ----------------------- 1 | i_11 | i_12 | i_13 2 | i_21 | i_22 | i_23 3 | i_31 | i_32 | i_33 .. | ... | ... | ... SELECT col1 FROM table WHERE id…
jozi
  • 316
  • 1
  • 2
  • 14
1
vote
0 answers

Ecto: subquery must return a single field

How do I translate this into ecto? SELECT * FROM mytable WHERE (url,"when") IN ( SELECT url, MAX("when") FROM mytable GROUP BY url ) it works fine in postgresql shell, but I get a Ecto.QueryError at runtime: subquery must return a single field…
Filip Haglund
  • 13,919
  • 13
  • 64
  • 113
1
vote
4 answers

Get distinct max date using SQL

I'm not sure quite how to title my question but this is what I'm trying to do: Given pc_tmppl_tbl pc_tmppl_attach pc_tmppl_val1 pc_tmppl_crtdt AJC05-06 AJCINT 2005-08-15 10:32:03.790 AJC06-07 AJCINT 2006-10-17…
mellerbeck
  • 199
  • 2
  • 14
1
vote
0 answers

is it possible to find primary key column name, and use it in WHERE-clause of the same query?

Since I like to write the smallest amout of code when doing small lookups in the database, mostly for testing/checking purposes, I wrote a small "wrapper" for it. Instead of the following: $dbh->prepare("UPDATE table SET multiple = ?, fields = ?…
ThomasK
  • 2,210
  • 3
  • 26
  • 35
1
vote
1 answer

No Rows after a specific value

Date time Record ID Action 9/9/2018 7:03 102 Support 9/9/2018 7:03 102 hello 9/10/2018 7:03 103 Fetch it 9/10/2018 7:03 103 Ask it 9/10/2018 7:03 103 enable it 9/9/2018 5:03 104 support 9/9/2018 4:03 104 Fetch it 9/11/2018 7:03 …
user3369545
  • 310
  • 2
  • 14
1
vote
0 answers

java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Long (Hibernate)

I am trying to run a subquery in hibernate like this:- DetachedCriteria cartProductIds = DetachedCriteria.forClass(UserCart.class,"usercart") .add(Restrictions.eq("is_deleted", false)) …
Ayush Srivastava
  • 444
  • 1
  • 4
  • 13
1
vote
1 answer

SemanticException IN encountered with 0 children error in HIVE

Select t.id, sa.APPLICATION_ID, c.CODE_SID from table1 t join table2 c on c.ID = t.COSTTYPE and c.TYPE_LIST_ID IN (Select typel.TYPE_LIST_ID from table3 typel where typel.TYPELIST_TABLE_NAME = 'a' ) join table4 tl on t.id =…
Rumana
  • 21
  • 1
  • 4
1
vote
3 answers

Join vs. Subquery

I have looking around for Joins vs Subquery in terms of resources and performance, and the answer seems to be platform dependent. But nothing seems to talk about them in terms of BigQuery. When I expanded the range of my query to include 100s of GB,…
Flair
  • 2,609
  • 1
  • 29
  • 41
1
vote
1 answer

Why query within query results in duplicate records

I am unable to understand this. SELECT COUNT(*) FROM profiles WHERE profiles.status IN ('abc', 'man') AND profiles.id IN ( SELECT artifacts.item_id FROM artifacts WHERE artifacts.deleted_at IS NULL …
dnsh
  • 3,516
  • 2
  • 22
  • 47
1
vote
2 answers

MySQL Subquery Causing Server to Hang

I'm trying to execute the following query SELECT * FROM person WHERE id IN ( SELECT user_id FROM participation WHERE activity_id = '1' AND application_id = '1' ) The outer query returns…
Chris
  • 631
  • 1
  • 9
  • 17
1
vote
2 answers

Hive SubQuery and Group BY

I have two tables table1: id 1 2 3 table 2: id date 1 x1 4 x2 1 x3 3 x4 3 x5 1 x6 3 x5 6 x6 6 x5 3 x6 I want the count of each ids for table 2 that is present in table 1. Result id count 1 3 2 0 3 4 I am using this…
user2200660
  • 1,261
  • 3
  • 18
  • 23
1
vote
1 answer

Subquery calculation error?

Why is this query not working? $query = ("SELECT * FROM (SELECT *, ( (((endingLatitude - " . $h . ")(endingLatitude - " . $h . ")) /" . pow($r1, 2) . ") + …
Henry Harris
  • 161
  • 1
  • 11
1
vote
4 answers

postgres performance issue with nested sql sub-queries

To simplify the case let's assume there are the following 3 tables A(a_id), B(b_id,val_b), C(a_id,b_id,val_c) I need to find all a_id that have certain value pairs from B and C. Example find all a_id which have records (val_b='1' and val_c='2' and…
user1039384
  • 521
  • 7
  • 16
1
vote
1 answer

Subqueries (in / exists) to Joins

SELECT *, `o_cheque_request.member_id`, `o_cheque_request.wallet_id` FROM `o_cheque_request`, `o_member`, o_memberinfo` WHERE `o_cheque_request.member_id` = `o_member.member_id` AND `o_member.member_id` = `o_memberinfo.member_id` AND withdraw_date…
Siddharth
  • 369
  • 3
  • 7
1 2
3
…
8 9