Stack Overflow
Stack Overflow

Questions tagged [derived-table]

A derived table is a term in SQL for a set of records that result from one query that can be used in another query. Derived tables are useful in simplifying complex queries into a series of simpler steps. They are often a simpler alternative to using temporary-tables.

A derived table is a term in SQL for a set of records that result from one query that can be used in another query. Derived tables are useful in simplifying complex queries into a series of simpler steps. They are often a simpler alternative to using temporary-tables.

188 questions
0
votes
2 answers

MySQL Update with derived tables and ORDER BY

This question is a follow up question from Link. I have a table with person (id) and one characteristic (var0) at different timepoints t. At some timepoints the characteristic is missing and I would like to fill the gaps with the former value. Here…
giordano
  • 2,954
  • 7
  • 35
  • 57
0
votes
2 answers

MYSQL ERROR 1248 I'm stuck

Ok, so I'm new to derived tables. When I run this query it gives me a MYSQL ERROR 1248. Anyone know of a work around for this? This is probably the most complex query I have done and I just want to get it to work. Thank you! delete from table_1 …
Cody Esmay
  • 11
  • 2
  • 4
0
votes
1 answer

MySQL query with derived Table Query runs faster than without it

I have a MySQL Query. When I run without derived table, the execution time is about 6 sec. However, if I run with derived table, the execution takes less than 1 sec. I have seen the EXPLAIN SELECT PLAN of the query. I do not get much out of it. I…
kayra
  • 228
  • 3
  • 10
0
votes
2 answers

SQL Database Functions

I am trying to use this to count how many tweets has each user contributed and put it in a new column called numTwt: SELECT screnname, count(*) AS numTwt FROM Tweet GROUP BY Tweetid ORDER BY numTwt But it seems it doesn't work.
user3351284
  • 31
  • 1
  • 9
0
votes
2 answers

Derived table in SQL Server

I have these two queries. I have no idea how to combine them together to make a derived table. I'm suppose to use the second query as the main query and use the first query in the FROM clause of the main query. SELECT EmailAddress, Orders.OrderID,…
jaramore
  • 389
  • 1
  • 3
  • 12
0
votes
0 answers

Multiple joins with grouping and derived tables

I've got four MySQL tables: contacts, orders, ordered_products and products and I'm trying to write some reporting queries. Here's one of them: SELECT t1.name, t1.contact_id, SUM(t1.prodSoldQty) AS contactSoldQty, SUM(t1.prodSoldVol) AS soldVol FROM…
Luca Anceschi
  • 2,257
  • 3
  • 19
  • 24
0
votes
0 answers

CTE in cursor gives "Connection is busy with results for another command" error while derived table does not

I ran into an error recently while fetching from a cursor on a query that utilized a CTE. "[Microsoft][SQL Server native Client 10.0]Connection is bust with results for another command" The error was occurring on a subsequent cursor that is…
StrykerJW
  • 21
  • 4
0
votes
2 answers

Optimizing a MySQL query with a large IN() clause or join on derived table

Let's say I need to query the associates of a corporation. I have a table, "transactions", which contains data on every transaction made. CREATE TABLE `transactions` ( `transactionID` int(11) unsigned NOT NULL, `orderID` int(11) unsigned NOT…
Johannes Gorset
  • 8,715
  • 4
  • 36
  • 34
0
votes
1 answer

Active Record query with complex join on a derived-table?

I'm working with Ruby, Sinatra and MySQL. I have four tables. Contacts (customers): class Contact < ActiveRecord::Base attr_accessible :id, :company_id, :name, :address, ... has_many :orders, :dependent => :destroy has_many :ordered_products …
Luca Anceschi
  • 2,257
  • 3
  • 19
  • 24
0
votes
0 answers

Select from derived table freezes phpmyadmin

I have a table Doctor, which has 30 000 records. I was wondering, why does this simple SQL query: SELECT * FROM ( SELECT * FROM Doctor ) AS tmp take at least 5 minutes to execute in phpmyadmin (it is killed after 5 minutes of executing by default,…
Jurom
  • 1,691
  • 1
  • 11
  • 10
0
votes
1 answer

NHibernate + join to derived table

In a table that stores multiple rows per employee, I want to pull one row per employee that represents the most recent entry for each employee. Here's where I am with hand-written SQL: SELECT [all the selected columns here] FROM Nominations t …
BryanB
  • 117
  • 2
  • 3
0
votes
1 answer

Is there any way to do a subselect from a derived table?

I have a situation where I'm constructing a derived pivot table and I'd like to be able to then do subselects against it based on various criteria (in effect making a pivot-pivot table). So... in pseudo it would look something like this... select …
Yevgeny Simkin
  • 27,946
  • 39
  • 137
  • 236
0
votes
1 answer

Jaspersoft Derived Table error when running SELECT query

I'm currently running a SELECT query in the derived tables part of creating a domain in Japsersoft's AWS BI Suite. However when I do, I get the following error: com.jaspersoft.commons.semantic.metaapi.MetaDataException: Cannot execute JDBC…
Tim Pegden
  • 43
  • 1
  • 1
  • 4
0
votes
3 answers

SQL - unknown column in derived table

This is the problematic part of my query: SELECT (SELECT id FROM users WHERE name = 'John') as competitor_id, (SELECT MIN(duration) FROM (SELECT duration FROM attempts WHERE userid=competitor_id ORDER BY…
user10099
  • 1,345
  • 2
  • 17
  • 23
0
votes
2 answers

Add new column to a derived table in view

I am trying to figure out how I can add a new column to a derived table in a view without modifying the underlying tables. Let me explain what I have to do: I need to add a simplified MQSA module to an existing RIS application that runs on SQL…
user1108996
  • 275
  • 2
  • 4
  • 13
1 2 3
…
12 13