CODEWITHSUNDEEP
sql
user2001006
user2001006

Reputation: 57

select statement w/ TWO AS aliases?

I am not a sql master, and this statement just doesnt make sense to me. So if ANYONE could break it down for me and let me know exactly what is what, I would GREATLY appreciate it.

Heres the statement:

SELECT a.accountid,a.firstname,a.lastname,a.miName,a.legacyAccountid,a.accountType,
SELECT count(invoices.invID) AS InvCount 
FROM invoices 
WHERE invoices.accountid = a.accountid AND invoices.statusID=0 AND invoices.Remaining>0
AND dueDate < DATEADD(Day,1,GETDATE()) AS AccountStatus,
SELECT count(lotOwnership.lotNumb) AS LotCount FROM lotOwnership WHERE lotOwnership.accountid = a.accountid AS Active
FROM a
WHERE a.accountid LIKE % var % OR a.legAccountid LIKE % var % OR a.lastname LIKE % var % AND a.accountType=1 ORDER BY a.lastname, a.firstname

What I would LOVE to know is:

How in the figgin frack is this legit?? Im refering to the whole statement but in particular this nested select:

SELECT count(lotOwnership.lotNumb) AS LotCount FROM lotOwnership WHERE lotOwnership.accountid = a.accountid AS Active

For some reason I cant wrap my head around this. Somebody please save me! I dont understand how one select statement can have TWO AS aliases???????????????????

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The code is missing parentheses around the subqueries and quotes around strings:

SELECT a.accountid,a.firstname,a.lastname,a.miName,a.legacyAccountid,a.accountType,
(SELECT count(invoices.invID) AS InvCount 
 FROM invoices 
 WHERE invoices.accountid = a.accountid AND invoices.statusID=0 AND invoices.Remaining>0
 AND dueDate < DATEADD(Day,1,GETDATE())
) AS AccountStatus,
(SELECT count(lotOwnership.lotNumb) AS LotCount FROM lotOwnership WHERE lotOwnership.accountid = a.accountid
) AS Active
FROM a
WHERE a.accountid LIKE' % var %' OR a.legAccountid LIKE '% var %' OR a.lastname LIKE '% var %' AND a.accountType=1
ORDER BY a.lastname, a.firstname

I did a minimal reformatting of the ugly coding style so you could see where the changes are. Any idea why these characters are missing?

EDIT:

Consider the first two lines of the select (now better formatted):

SELECT a.accountid, a.firstname, a.lastname, a.miName, a.legacyAccountid, a.accountType,
       (SELECT count(invoices.invID) AS InvCount 
        FROM invoices 
        WHERE invoices.accountid = a.accountid AND
              invoices.statusID = 0 AND
              invoices.Remaining > 0 AND
              dueDate < DATEADD(Day,1,GETDATE())
      ) AS AccountStatus,

The subquery is defined to return a column named InvCount. If you ran it separately, it would have a column with the name InvCount.

In this context it is a scalar subquery. That is, a subquery that returns (up to) one row and one value. In that context, the returned value is treated as the result of an expression. So, the column named InvCount in the subquery is named AccountStatus in the outer query. You could think of this as:

select ((select  . . . ) as InvCount) as AccountStatus

Of course, that syntax is not legal, but it captures conceptually what happens. The first alias is simply ignored.

Upvotes: 1

sion_corn
sion_corn

Reputation: 3141

The correlated subqueries needed to be surrounded by parentheses. Then, it makes sense because they produce scalar values, which are then aliased with the AS.

If your code in the OP runs, then the application must know how to isolate the subqueries without the added parentheses.

SELECT 
    a.accountid,
    a.firstname,
    a.lastname,
    a.miName,
    a.legacyAccountid,
    a.accountType,
    (SELECT 
        count(invoices.invID) AS InvCount 
        FROM invoices 
        WHERE invoices.accountid = a.accountid 
        AND invoices.statusID=0 
        AND invoices.Remaining>0
        AND dueDate < DATEADD(Day,1,GETDATE())) AS AccountStatus,
    (SELECT 
        count(lotOwnership.lotNumb) AS LotCount 
        FROM lotOwnership 
        WHERE lotOwnership.accountid = a.accountid) AS Active
FROM a
WHERE a.accountid LIKE % var % 
    OR a.legAccountid LIKE % var % 
    OR a.lastname LIKE % var % 
    AND a.accountType=1 
ORDER BY a.lastname, a.firstname

Upvotes: 2

Related Questions