StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAlias scoping in a (Correlated) subquery embedded in the select statement
text
Body
copied!<p>My work is running SQL Server 2008 and I spend a lot of time querying the database for information as a side piece to my job. If I need information that isn't at the same aggregate level as my dataset I use an embedded query in the select statement. Usually it's 2 or 3 slightly different versions of the same number, so they both query the same tables. (See example below)</p> <p>The question is <em>what is the scoping of the aliases for the subqueries embedded in a select statement</em>. The two options I've thought of are:</p> <ol> <li>At the Script level and must be unique to all subqueries and tables? </li> <li>At the Subquery level and can share the same aliases in each.</li> </ol> <p>I know for tables aliased in the From statement they must be unique. I thought that the fact that the queries were executing on each row generation that it might be a different situation. </p> <p>Examples (completely made up, let me know any obvious errors and I'll correct them):</p> <p>Script Level - unique aliases for all subqueries and tables:</p> <pre><code>Select p.purchaseid, p.purchasedate, s.storename, c.customerid, (select count(p2.purchaseid) from purchases p2 inner join store s2 on p2.storeid = s2.storeid where s2.storeid = s.storeid and p2.purchasedate = p.purchasedate) as 'Store Daily Total Purchases', (select count(p3.purchaseid) from purchases p3 inner join store s3 on p3.storeid = s3.storeid where p3.customerid = p.customerid and p3.purchasedate = p.purchasedate) as 'Customer Daily Total Purchases' from purchases p inner join customer c on p.customerid = c.customerid store s on p.storeid = s.storeid </code></pre> <p>Query Level - common aliases for subqueries ok:</p> <pre><code>Select p.purchaseid, p.purchasedate, s.storename, c.customerid, (select count(p2.purchaseid) from purchases p2 inner join store s2 on p2.storeid = s2.storeid where s2.storeid = s.storeid and p2.purchasedate = p.purchasedate) as 'Store Daily Total Purchases', (select count(p2.purchaseid) from purchases p2 inner join store s2 on p2.storeid = s2.storeid where p2.customerid = p.customerid and p2.purchasedate = p.purchasedate) as 'Customer Daily Total Purchases' from purchases p inner join customer c on p.customerid = c.customerid store s on p.storeid = s.storeid </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload