SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

learn more… | top users | synonyms

2
votes
1answer
61 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
0
votes
1answer
67 views

Can this where in subquery be optimized?

Currently the query takes ~ 0.2 seconds. Is this the best it gets? Because it feels rather slow for the amount of data I have in my database. I've tried creating an inner join out of it but I can't ...
0
votes
0answers
41 views

subqueries with select

This below query works as expected: SELECT reports.* FROM reports INNER JOIN units ON units.id = reports.unit_id WHERE reports.unit_id IN (1111, 1112, 1113) ...
1
vote
2answers
83 views

How do I add an alias to the table

SELECT MAX(COUNT), A.id FROM ( SELECT supplier_id as id, COUNT(*) AS count FROM `library`.`lms_book_details` GROUP BY supplier_id ) GROUP BY A.id; While executing the query, MySQL gives ...
0
votes
2answers
148 views

MAX from Subquery [closed]

How do I find the maximum value of a particular column from the result of a SubQuery? For example, if the result of the subquery is: 10 20 30 How do I get maximum from this, in this case 30?
1
vote
1answer
655 views

Postgres, levenshtein distance and nested queries

I have a DB Table with 3,000,000 rows or more like that: |ID|T4|T16|T64|TL where ID =INTEGER (PK) and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4) ...
1
vote
2answers
976 views

Improve performance with the WHERE NOT IN sub-select clause

In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year. ...
0
votes
1answer
380 views

Trigger - Add to a date using a subquery

(postgresql 9.2) I'v 2 tables Table 1 (extinguisher) extinguisher_id (serial) type_designation_extinguisher_type(text) manufacturing_date (date) life_date(date) Table 2 (extinguisher_designation) ...
0
votes
0answers
219 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
3
votes
3answers
18k views

How to select/count rows where the same value exists in a column but not in other column all at the same table

I have this table structure and data: CREATE TABLE IF NOT EXISTS `default_relations_users` ( `id_user_rq` int(11) NOT NULL, `id_user_ap` int(11) NOT NULL, UNIQUE KEY `rusers_rq_ap_idx` ...
0
votes
2answers
125 views

Subquerying results in no row returned although records exist

I have two tables, namely Employees and Payments. Below is the contents of the two: The Employees table EmpID |EmpName ------ -------- 113 Harry The Payments table EmpID | Amount | Period ...
2
votes
0answers
904 views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
1
vote
1answer
77 views

Counting number of visitors who did NOT log in

I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are ...
3
votes
1answer
2k views

Why does SQL Server run a subquery for each row of the table it's qualifying?

This query runs in ~21 seconds (execution plan): select a.month , count(*) from SubqueryTest a where a.year = (select max(b.year) from SubqueryTest b) group by a.month When the subquery ...
12
votes
1answer
324 views

Why does subquery use parallelism and join doesn't?

Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete. Join ...
0
votes
0answers
310 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
1
vote
1answer
1k views

How to get MySQL to iterate a subquery in an aggregate select?

Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does. Consider the following two simple queries: #1 ...
1
vote
1answer
1k views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
1answer
461 views

How to UPDATE a column by COUNT of FK from another table?

How to extend this query for the entire table, to update all ids? UPDATE table1 SET number=( SELECT COUNT(*) FROM table2 where id=1 ) WHERE id=1 id is the PRIMARY KEY in table1 and FK in table2. ...
0
votes
1answer
143 views

Combine results from two queries?

I need to combine the results from two queries into one result set and order them altogether. This is the working, raw SQL code I have: SELECT * FROM ( SELECT t1.id AS entity_id, ...
0
votes
1answer
1k views

How to get performance benefits from a view vs subquery?

I'd like to know how/if anyone has gained significant performance benefits from using views instead of subqueries. I would prefer not to define a specific case, since I'm trying to establish good ...
1
vote
1answer
5k views

Mysql: update query with subquery

Hello i need to perform the following query Query update Taxonomy set sourceId = ( select id from TaxonomyMapping a where a.oldId = ( select cm.en_ID from TaxonomyMapping ta ...
0
votes
1answer
608 views

How to update a table by count from another table?

I have two tables as CREATE TABLE country_statistics ( id int(11) NOT NULL AUTO_INCREMENT us int(11), uk int(11), ca int(11), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CREATE TABLE entries ( id ...
2
votes
1answer
2k views

SQL Server select with regex

Can we use regular expression to select the item from database? The table item is like below Table column|name| 10.01.02 | a | 100.2.03 | b | 1021.10.04 | c | Now my problem is that i ...
1
vote
2answers
182 views

Using max for each sub group does not seem to work.

I have a table that looks like this in Oracle 11g: +----------------------------------------------------------+ | ACCT_NBR | MAIL_TY | ORGA | PERS | RUN_DATE | ...
1
vote
3answers
3k views

How do I use subquery on the same table in MySQL?

I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now; I'm hoping I could ...
1
vote
1answer
263 views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
1
vote
1answer
6k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
2
votes
1answer
350 views

Does MySQL have problems with nested insert like with subqueries in where?

Query 1: INSERT `personal`.`locations` SELECT DISTINCT `s`.* FROM `references` `t` JOIN `locations` `s` ON `first_id` = `s`.`id` WHERE `lat` >= 37.3 AND `lat` <= ...
0
votes
2answers
89 views

Query keeps on executing

I'm executing below query, but it keeps on executing. In place of some time I have a time value. select distinct(col1) from table1 where col2 > 'some time' and col1 not in (select ...
2
votes
1answer
1k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
1
vote
1answer
927 views

MySQL subqueries that use range based on values of main queries don't use indices properly

I think I've isolated a problem that has been affecting many of my queries lately. And would like some help to figure out a solution for this. Ok so my findings are that a normal query that runs very ...
0
votes
2answers
6k views

Use an Alias in Where Clause Subquery

I need to show some fields from another table in Oracle. Here is my query: SELECT ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE, ANGGARAN.SIMPEG_PEGAWAI.NAMA, ANGGARAN.SIMPEG_PEGAWAI.NIP, ...
-4
votes
0answers
46 views

oracle database query [closed]

I have a problem in writing a Database Query Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS Data: REQUEST_STATUS may contains ...
0
votes
2answers
1k views

How to use and optimize subquery on 100 million rows

What I'm trying to do is running a job on more than 100 million domains which haven't processed before. I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
0
votes
3answers
629 views

Not getting the expected result with 'IN' and subquery

I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the ...
1
vote
1answer
58 views

Identical subquery optimisation in an update

update activitybooking set `submitted`='1' where id='958' and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and (id='958' or ...
8
votes
3answers
985 views

SQLITE: A problem of tags and products

I'm searching for a way to create a query to do the following: Let's consider 3 tables: products: List of products tags: list of tags tag_ties: table used to associate a tag to a product Let's ...
1
vote
1answer
417 views

Getting max values from MySQL tables

I have three tables: competitions (id, name, date) athletes (id,name) results (place, id_athlete, id_competition, ranking_points) where: results.id_athlet=athlet.id ...
2
votes
1answer
1k views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
2
votes
3answers
3k views

How do i subtract a number of days from a prepared statement parameter in db2?

I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( ...
2
votes
2answers
1k views

Why do I need to use a sub query to filter down a grouped select?

If I do this -- SELECT dv.Name ,MAX(hb.[DateEntered]) as DE FROM [Devices] as dv INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID WHERE DE < '2013-03-04' GROUP BY dv.Name ...
1
vote
1answer
81 views

Why is this query so slow?

I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only ...
0
votes
1answer
704 views

Delete with subquery with no unique columns

I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns. Database is SQLite delete from LIKES where ID1 = 10 /// Wrong there more then one record with ID1=10 delete from LIKES ...
4
votes
1answer
75 views

does a subquery pull the entire table?

i am attempting to run a subquery in a table: SELECT t1.*, t2.* FROM t1 LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 on t1.col1 = t2.col1 WHERE ... My main question is, since that subquery ...
1
vote
1answer
1k views

Postgresql nested row_to_json not working

This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like ...
2
votes
3answers
2k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, ...
0
votes
0answers
41 views

Consolidate does not work with my subquery [duplicate]

Possible Duplicate: SQL Subquery , One to Multiple rows Following query I have written returns the result as expected, example 121350 rows. SELECT ...
1
vote
1answer
341 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
0
votes
2answers
389 views

Tuning query to remove joins for reporting

This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server. SELECT user_id FROM user_table LEFT OUTER JOIN ( SELECT user_id , ...