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

4
votes
1answer
46 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
1
vote
1answer
270 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 ) = ( ...
1
vote
1answer
189 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
90 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 ...
1
vote
1answer
111 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. ...
0
votes
1answer
70 views

How to find the same salaries and department names?

Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following ...
0
votes
1answer
119 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
72 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, ...
2
votes
0answers
293 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 ...
0
votes
0answers
44 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
0
votes
0answers
30 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) ...
0
votes
0answers
45 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 ...
0
votes
0answers
101 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) ...
0
votes
0answers
36 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 ...