[thelist] Things Found while researching other things - Part 2
Rob Smith
rob.smith at lexjet.com
Fri Oct 27 12:20:54 CDT 2006
And my retort tip would be:
<tip type="WITH and SQL 2005" Author="Rob.Smith>
In SQL 2005, you can use the keyword WITH as an alias:
WITH Sales
AS
(
Select
total_sales,
total_margins,
commission_id
FROM
SalesInvoices
UNION ALL
Select
total_sales*-1,
total_margins*-1,
commission_id
FROM
ReturnMemos
)
SELECT
sum(S.total_sales) as [Total Sales],
sum(S.total_margins) as [Total Margins],
E.Employee_Name as Name
FROM
Sales S INNER JOIN Employees E on
S.commission_id = E.commission_ID
</tip>
Also comes in handy on recursive queries.
Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236
More information about the thelist
mailing list