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
0
votes
1 answer

SQLite query with literal values

I have a 2 tables A and B in SQLite A: Name|Badge ---------- ABC |X XYZ |Y B: ListCode |Badges -------------------- V |'X','Y','Z' I want to do something like this: SELECT * FROM A WHERE BADGE IN (SELECT BADGES FROM B WHERE LISTCODE =…
Kenter
  • 47
  • 1
  • 7
0
votes
1 answer

How to have 2 columns in a subquery but ignore one of them?

I'm trying to execute this query on my database which is querying 2 tables based on the results of a third table. SELECT * FROM ads_user AS u INNER JOIN ads_medium AS m ON u.id = m.owner_id WHERE m.id IN (SELECT medium_id, …
2hamed
  • 8,719
  • 13
  • 69
  • 112
0
votes
2 answers

Below query doesnt give me expected result please help me out

The select query has to display seperate row for every value it gets but on executing this query it yields only first value from the sub query used in IN clause. SELECT prod_id FROM tbl_product WHERE tbl_product.prod_status = 1 AND…
OM The Eternity
  • 15,694
  • 44
  • 120
  • 182
0
votes
0 answers

Mysql Update select subquery vs trigger loop

Hello guys. I've an issue with a simple query. Here we go, that's the code. UPDATE user_resources AS ures LEFT JOIN user_buildings as ub ON ub.city_id = ures.city_id INNER JOIN building_consumption AS…
Jim Tebstone
  • 552
  • 6
  • 13
0
votes
4 answers

MySQL - Update by using a value from sub query on same table

I have a table 'mytable' and it has following structure and sample data. +----+------------+--------------------+ | id | name | password | +----+------------+--------------------+ | 1 | Raj | somepwd …
Raj Kumar Boddu
  • 85
  • 3
  • 12
0
votes
1 answer

SQLite : Need reference to "current" row in a UPDATE

I'm working with a SQLite table representing a tree. Its columns include id and parent. Of course, each parent value is the id of another row. There is also a changeCount column, which I must increment by 1 for each child deleted in a bulk…
Jerry Krinock
  • 4,860
  • 33
  • 39
0
votes
3 answers

Rewrite IN subquery as JOIN

I've never had good performance with IN in MySQL and I've hit a performance issue with it again. I'm trying to create a view. The relevant part of it is: SELECT c.customer_id, .... IF (c.customer_id IN ( SELECT cn.customer_id FROM…
Matt McCormick
  • 13,041
  • 22
  • 75
  • 83
0
votes
1 answer

How to do a Query on SQL Server 2008 R2 using a subquery / function on GROUP BY

When I execute this query, it works fine: SELECT pr.pr_nombre , cl.cl_nomcorto, mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951 as Interes, mc_cant * dbo.tipo_cambio(cr.mo_id,3,mc_fecha)/0.951*.049 as WH, (select pr_id from…
Rolando M
  • 15
  • 4
0
votes
0 answers

Error in multiple data insert using sub query: Conversion failed converting varchar to int

I am trying to insert more than one row in a table which uses the select statement as a sub query. It works fine when I insert only one row but gives an error when I try to insert more than one row. INSERT INTO sysdba.ACTIVITY ([ACTIVITYID],…
Jayanta Rijal
  • 69
  • 1
  • 8
0
votes
4 answers

SQL subquery matches hard-coded IN criteria, but not subquery

I have a group of people who have taken a test. I can select their IDs with this query: SELECT person_id FROM tests WHERE test_code = 1234 I'd like to pull these individuals' records from a demographics table, so I tried this subquery to do…
Matt Parker
  • 26,709
  • 7
  • 54
  • 72
0
votes
1 answer

How select unique minimum value from more tables

I have following problem. In MySQL I have two tables: Table A --------------------------- | idOffer| price | 4 | 20 | 4 | 30 | 5 | 15 | 5 | 18 | 6 | 6 | 4 | 9 -------------------------------------------------- Table…
XWizard
  • 319
  • 6
  • 17
0
votes
1 answer

want to use between clause in subquery

i want the record of employee who applied leave form '2016-03-16' date to '2016-03-25' date. But when i will write my query in which i would mention the between clause in where condition,like i mention the date select * from leave_form where…
shyarry g
  • 345
  • 1
  • 3
  • 8
0
votes
3 answers

optimise IN subquery

I have a workers table and an associated workerGeofence table. CREATE TABLE IF NOT EXISTS `workergeofences` ( `ID` int(11) NOT NULL, `WorkerID` varchar(20) NOT NULL, `GeofenceID` int(11) NOT NULL, `isActive` tinyint(4) NOT NULL ) ENGINE=InnoDB…
user2363025
  • 6,365
  • 19
  • 48
  • 89
0
votes
2 answers

Alternative logic for a subquery as part of a condition

I have two tables: T1 key code1 code2 code3 1 A A A 2 B B G 3 A B C 4 C C C 5 D E F 6 E E E 7 A D G 8 G G …
Danzo
  • 553
  • 3
  • 13
  • 26
0
votes
1 answer

mysql select columns having complex count + hour-range criteria

Issue Description: I have the following query to retrieve the latest alarms in last 15 minutes. SELECT AlmCode,OccurTime,ClearTime....columnN FROM TB_ALM WHERE AlmCode IN ('3236',....'5978') AND OccurTime >= date_sub(NOW(),interval…
Siva
  • 294
  • 1
  • 9
  • 25
1 2 3
…
8 9