derived table -section 3

Note: T-SQL code is included for reference only not to be edited.
Edit starts here:
----------------------------------------------------------
The derived table is a subquery in a FROM clause. The FROM clause must have an aliased name. It is called a derived table because the fields in the outer SELECT clause query come from this named derived table. There are Inline and External derived table subqueries:

In an inline single derived table, it is mandatory to give an alias to the table in the FROM clause.
In an external single derived table, it is mandatory to list the field name inside () after the FROM table alias name. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html

Product  ProdType                                    -- Inline single derived table subquery, example 3.1
A	      x
B	      x
B	      xx
C	      x
D	      x
D	      xx
E	      x 
-- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
ProdType  ProdType
B	      x
B	      xx
D	      x
D	      xx

Select Product, ProdType
from (
  select *, count(*) over (partition by Product) cnt from #t
) d where cnt > 1;

-- Note: This example will be used later in discussing CTEs.

Open in new window


10a.pngThe obvious limitation with derived tables is that we cannot have multiple table expressions in a FROM clause whereas with CTEs discussed later we can. Of course we can nest derived to include multiple derived tables but nesting complicates the coding and makes it prone to syntax errors.  

-- Multiple derived tables syntax 
-- (for reasons stated this is possible but not used):

SELECT f1, f2, ...                       
FROM (SELECT ...  
      FROM (SELECT ...  
            FROM Ta 
            WHERE ...) AS Tb
      WHERE ...) AS Tc
WHERE ...) As Td (f1, f2, ...);

Open in new window


Edit ends here:
----------------------------------------------------------
also see: http://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

Note, Questions on the remaining sections will be added later.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
Remove 10.png?
Change "nest derived" to "nest derived tables"?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Remove 10.png? done.

re:> hange "nest derived" to "nest derived tables"?
This part required rephrasing the entire paragraph. Please review the following:
------
The self imposed limitation with derived tables is that we don't use multiple table expressions in a FROM clause whereas with CTEs discussed later we can. Of course, technically speaking, we could nest multiple derived tables as shown below but nesting them in this manner complicates the coding and makes it prone to syntax errors thus rarely used.
-------
Mark BullockQA EngineerCommented:
Looks good.
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I really appreciate your time and effort.

Regards,

mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Proofreading

From novice to tech pro — start learning today.