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
0answers
72 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here ...
0
votes
1answer
40 views

Passing filter params to a sub query

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to ...
3
votes
1answer
358 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
0
votes
1answer
195 views

Select records as groups based on sequence and date

I have data stored as: CompID | Seq | Udate | Utime --------+-----+-----------+------- mx433 | 1 | 1/1/2012 | 13:54 mx433 | 1 | 1/2/2012 | 13:43 mx433 | 2 | 1/4/2012 | 14:23 mx433 ...
0
votes
2answers
28 views

In MySQL, get number of pregnant women who received 3 ANC checkups

I am working on a project where it is required to find out the total number of pregnant women who have received 3 ANC(Ante-Natal Care) checkups done. A pregnant women is said to receive 3 ANC ...
0
votes
2answers
75 views

Insert query with a subquery

I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly ...
0
votes
0answers
52 views

Smallest unique string among multiple columns

Somewhat similar to the Largest Common Substring (http://en.wikipedia.org/wiki/Longest_common_substring_problem) I'm trying to find the smallest unique string from a table. In some tables, its just ...
2
votes
1answer
6k 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 = ...
0
votes
1answer
63 views

Using a subquery in a LIKE statement

I need to write a subquery inside a LIKE statement. This subquery would return me a word that I need to find in the middle of a sentence (that belongs to another field). Is it possible? This is how ...
4
votes
1answer
151 views

Combine multiple queries into 1 query

I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages: \d messages Table ...
6
votes
3answers
3k views

Check if any of the values is in a subquery result

I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
1
vote
1answer
412 views

Call function where argument is a (sub)select statement

My function takes an int4 as argument and returns a table: SELECT * FROM test_function(545421); -- works fine SELECT * FROM test_function(SELECT customerid FROM tableX ...
0
votes
0answers
86 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
1
vote
0answers
315 views

Amazon Redshift: “*SELECT* 1” vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on ...
0
votes
0answers
31 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
0
votes
1answer
95 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
1
vote
1answer
136 views

Alias of Subquery not working

I have been trying to get an order by to work correctly with an aggregate function. i.e. order by (bp + points) I also try adding these as a total amount from the following query however. If I try ...
1
vote
1answer
100 views

MySQL Update a table based on the last data from another table while grouping with conditions

I need a query to UPDATE a series of fields based on another table Table lp_plates_backup | uid | brand | model | | 1 | | | | a | Old | Error | ...
0
votes
1answer
34 views

Pick up the name of the column by two diff column in single table

Thank you guys in advance! i am desperately need to display my data in this way, My database structure is, Column idcat, parent_id and child_id is sharing single column name, catname. I tried ...
3
votes
1answer
493 views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
-2
votes
1answer
110 views

Single-row subquery returns more than one row

I try to run the follwoing query but it gives me 'Single-row subquery returns more than one row' error. BEGIN FOR C2 IN (SELECT S.bom_header, S.sap_cage_cd, ...
0
votes
2answers
273 views

Match multiple components of a subquery CTE in PostgreSQL

I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
1
vote
1answer
174 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
0
votes
3answers
3k 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
2answers
55 views

Delete the second record where a user has two records

I have a simple database scheme, which due to previously being badly set up has numerous duplicate records. record_table |--------------------------------------------------------| | id | subid | ...
1
vote
1answer
14k views

MYSQL query to find records between two date range

I am working in a web application using PHP and MySQL. I have two tables Employee employee_id email_id 3 tl1@jasmine.com 4 agent1@jasmine.com 5 ...
0
votes
1answer
75 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1], tblClient.[Address 2], tblClient.City, tblClient.State, tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM tblClient ...
0
votes
1answer
27 views

Querying All Records of All Users who have any instance of purchase behavior in their history

I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account. I have one table, "users" that shows ...
1
vote
1answer
85 views

Determine existence in recursive working table

I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is ...
0
votes
0answers
99 views

Multiple subqueries performance issue

I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer. The query looks like that: SELECT c.Id ...
0
votes
1answer
346 views

How to make a single row result query to return multiple rows

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography. I also ...
1
vote
1answer
99 views

Is it possible to emulate the WITH command in MySQL 5.1.73? [closed]

I have the following query: SELECT COUNT(*) AS `count` FROM agreements WHERE start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y') AND start_date < ...
0
votes
1answer
125 views

problem with query two different tables with foreign key relations

I am having a problem with querying two tables with different foreign key relations... My first query works fine but when adding another table with a different set of relations I am having a ...
2
votes
1answer
7k views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
1
vote
1answer
8k 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 | ...
0
votes
1answer
268 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, ...
3
votes
2answers
459 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
1
vote
2answers
3k 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
45 views

Email open rate calculations in subqueries with postgres

So I'm relatively new to PostgreSQL, but here we go: I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays ...
2
votes
1answer
155 views

MySQL: Why queries with subqueries are much faster than single query?

I made few queries on my db lately First query: SELECT `table`.* FROM (`table`) WHERE table.id in(27172172,[...bunch of ids...],27171770) ORDER BY field (`table`.`id`, ...
1
vote
1answer
50 views

Slow execution of a subquery with 0 matches

In MySQL, I have two basic tables - Posts and Followers: CREATE TABLE Posts ( id int(11) NOT NULL AUTO_INCREMENT, posted int(11) NOT NULL, body varchar(512) NOT NULL, authorId int(11) NOT ...
0
votes
2answers
130 views

Solution on a complex sub select query SQL Server

I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this: Select *, (Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA, ...
1
vote
1answer
71 views

Select products and include all cities it belong to

I have 2 tables : PRODUCTS +----------+----------+----------+----------+ | id | title | content | city | +----------+----------+----------+----------+ CITIES +----------+----------+ | ...
0
votes
1answer
11k views

Identify faulty sub-query (subquery returned more than 1 value)

I am trying to resolve an issue with a query that fails with this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when ...
0
votes
4answers
106 views

Why does this query take 32 sec to execute?

I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
0
votes
1answer
66 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
0
votes
2answers
187 views

Update specific records of table based on subquery's output (sql server)

have this query which produce below output : select sec.site,sec.Name,sec.Freq from ( select Name from sec group by name having count(Freq) > 3)as dt inner join sec on dt.Name = sec.Name where ...
2
votes
1answer
1k views

Select multiple columns in subquery

I am having trouble selecting all columns in a sub query and can't seem to get anywhere with joining for replacement. My Table Data is: CREATE TABLE `w7nwd_com_mtapp_product_engine` ( `id` ...
1
vote
1answer
246 views

Oracle: summing column value with subquery?

Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this ...
0
votes
2answers
121 views

Subquery or multiple left joins, or both?

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...