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
3
votes
1 answer

Inserting more than 1000 rows using the table value constructor as a derived table

I have the following T-SQL query which inserts a list of values into a temporary table. My issue is that the INSERT function is limited to 1,000 rows and I have a list of 4,000 rows. Here is an extract of the query: USE MyDatabase create table #t1…
user3115933
  • 4,303
  • 15
  • 54
  • 94
3
votes
1 answer

Subquery's rand() column re-evaluated for every repeated selection in MySQL 5.7/8.0 vs MySQL 5.6

I am doing a subquery in which I have a calculated column involving random number generation. In the base query I select this column twice. MySQL 5.6 works as I expect, the calculated value being called once and fixed. The 5.7+/8.0+ execution seems…
Pavel S.
  • 1,202
  • 1
  • 13
  • 29
3
votes
3 answers

can't merge a union all view

I know Oracle RDMS can't merge a view that has a set operator in it. I want to know why is that. For example, this: SELECT u.* FROM ( SELECT a.a1 A, a.a2 B FROM tab_a a UNION ALL SELECT b.b1 A, b.b2 B FROM…
milan
  • 63
  • 6
3
votes
2 answers

multiple count on derived table

I am trying to count total shortlisted and total interested by using a derived table but it's not working. select (SELECT COUNT(ID) FROM Dtable WHERE Is_shortlisted=1) AS TOTALSHORLISTED, (SELECT COUNT(ID) FROM Dtable WHERE…
shahid zaman
  • 122
  • 9
3
votes
3 answers

Inner Joins with Derived Tables

I have a question about the basic syntax of joining with derived tables. Is this teh basic syntax that is used: select * from table1 a inner join (select * from table2) as T1 on 1.ID = T1.ID Will that work?
Neil Keicher
  • 39
  • 1
  • 1
  • 5
3
votes
1 answer

A Derived Field in an SQLite3 Database

Good Evening everyone, Today I would like to ask a question relating to derived fields (also known as calculation fields) in SQLite3. Utilizing two values stored within my database "weight" and "distance" I know it is possible to utilize them to…
user3075454
  • 49
  • 6
3
votes
3 answers

MySQL union within derived table (related_id=a AND related_id=b) OR (related_id=z)

I have the following tables: users, tags, tags_data. tags_data contains tag_id and user_id columns to link the users with tags in a 1 user to many tags relationship. What is the best way of listing all users that have either tag_id 1001 AND 1003, OR…
batfastad
  • 1,943
  • 3
  • 27
  • 37
3
votes
7 answers

Unfamiliar character in SQL statement

This is sort of SQL newbie question, I think, but here goes. I have a SQL Query (SQL Server 2005) that I've put together based on an example user-defined function: SELECT CASEID, GetNoteText(CASEID) FROM ( SELECT CASEID …
cori
  • 8,666
  • 7
  • 45
  • 81
3
votes
4 answers

Update with Sub Query Derived Table Error

I have the following SQL statement to simply update the #temp temp table with the latest package version number in our Sybase 15 database. UPDATE t SET versionId = l.latestVersion FROM #temp t INNER JOIN (SELECT gp.packageId …
markblandford
  • 3,153
  • 3
  • 20
  • 28
3
votes
2 answers

Pass Value To Derived Table

Is there a way to pass a value to a derived table query? In the derived table I want reference a value ([docSVsys].[sID]) from the outer query. I get an error: Msg 4104, Level 16, State 1, Line 7 The multi-part identifier "docSVsys.sID" could…
paparazzo
  • 44,497
  • 23
  • 105
  • 176
2
votes
1 answer

Query using a derived table with ISNUMERIC results in conversion failure (varchar to int)

Here's an example query: DECLARE @table table (loc varchar(10)) INSERT INTO @table VALUES ('134a'), ('123'), ('abc'), ('124') SELECT * FROM ( SELECT * FROM @table WHERE ISNUMERIC(loc) = 1 ) as a WHERE CAST(loc as INT) BETWEEN 100 AND 200 If…
MikeM
  • 27,227
  • 4
  • 64
  • 80
2
votes
1 answer

Derived table used several times

I need to run 3 consecutive queries using the same derived table... I'm using MS SQL Server 2008 Select a,b,c from categories inner join (select x,y,z from derivedTable where…) … Select a,b,c from users inner join (select x,y,z from derivedTable…
user996760
  • 33
  • 4
2
votes
3 answers

SQL Help in Access – Looking for the Absence of Data

I am trying to find the fastest way to find all of the records in a parent table that do not have certain records in a child table. For example, I want the query to return all of the family records where there are no male children or no children at…
Shane Miskin
  • 1,911
  • 2
  • 22
  • 30
2
votes
1 answer

Correlated queries - In derived tables, can I have many nesting levels?

The main question and the question itself is: In derived tables, can I have many nesting levels? Context: I know that in MySQL you can't have correlated subqueries with two levels deep, but, using derived tables it seems that you can, is this normal…
user19481364
2
votes
3 answers

Replacing right join with derived table to left join

How do i write this query, with left join. since the framework i use doesn't support right join i need to rewrite the query. Can any one suggest me a possible solution. select Audit.history_id,Audit.field,modifiedtime,operation from Audit right join…
John
  • 2,682
  • 5
  • 23
  • 24
1
2
3
…
12 13