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.
0
votes
2answers
44 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 ...
0
votes
0answers
57 views
MySQL Pre-Filter Subquery
Good Evening All,
I'm trying to pre-filter a table such that the more complex MATCH ... AGAINST query doesn't need to process every row in the DB.
I tried obtaining a list of identifiers (query 3) ...
1
vote
1answer
57 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
118 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 ...
1
vote
1answer
43 views
MySQL Subquery returns more than one row
I have a table namely tbl_plays. It has the following columns and data
id(PK)| gameid | drawid | etc
----------------------------------------------
1 | 1509 | 73 | ...
11
votes
1answer
210 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
23 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
53 views
UPDATE a column by COUNT of another table
It might be a naive question, but what is the difference of these two queries and which is preferred?
UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = ...
1
vote
1answer
45 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
...
2
votes
0answers
253 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
41 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
29 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
53 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
103 views
Optimizing multiple MySQL sub-queries and joins for multiple runs
The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster?
Setup:
Mac OS X 10.7.5
MySQL ...
0
votes
1answer
100 views
Find nearest values from MySQL Table
I want to fetch nearest rates for a given value from below table.
mysql> select * from rates;
+------+----------+----------+
| ID | Type | Rate |
+------+---------------------+
| 1 | ...
0
votes
1answer
179 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
270 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
107 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
1answer
51 views
Need some advice creating this complex view
I need to create a view or query in MySQL to obtain if product has one or more product_detail. This are the create sentences for tables:
CREATE TABLE IF NOT EXISTS `stock` (
`product` INT NOT NULL,
...
1
vote
1answer
129 views
Oracle 10g: Optimizing an update query that uses a subquery
I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.
UPDATE TBL_REPORTING REP
SET (REP.ISSUER_ID, REP.ISSUER_NAME
) = ( ...
0
votes
1answer
152 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 ...
0
votes
1answer
902 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
1answer
199 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.
...
1
vote
2answers
73 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
1answer
144 views
Subselect dependent on first select value
I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select.
This example works fine for the main ...
1
vote
1answer
75 views
Minimizing Page Fetches
I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
0
votes
2answers
66 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 ...
-1
votes
1answer
918 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:
...
0
votes
1answer
86 views
Filter on a window function without writing an outer SELECT statement
Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. ...
-4
votes
0answers
43 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
524 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,
...
1
vote
3answers
870 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 ...
0
votes
2answers
445 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 ...
2
votes
1answer
381 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 ...
0
votes
3answers
103 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
43 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 ...
1
vote
1answer
359 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 ...
2
votes
1answer
400 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
2answers
329 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
69 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 ...
6
votes
2answers
2k views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT DISTINCT petid, userid,
(SELECT ...
0
votes
1answer
170 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 ...
2
votes
1answer
868 views
Select multiple columns in subquery
I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement.
http://sqlfiddle.com/#!2/2481e/1
The link is to the schema with my current ...
4
votes
1answer
65 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 ...
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
478 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
1answer
215 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` <= ...
1
vote
3answers
927 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,
...
3
votes
1answer
1k views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
0
votes
2answers
268 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 , ...
