Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 I don't understand the following sql,help!

Author  Topic 

debradeng
Starting Member

46 Posts

Posted - 2006-10-29 : 19:56:57
SELECT

a.CompanyId,
a.AccountId,
a.CompanyName,
a.BusinessName AS Expr5,
a.ABNNumber AS Expr6,
a.IsActive AS Expr7,

b.CompanyId AS Expr1,
b.AccountId AS Expr2,
b.Name AS Expr3,
b.CompanyName AS Expr4,
b.BusinessName,
b.ABNNumber,
b.IsActive,

c.BranchCompanyId,
c.IsActive AS Expr8,
c.CompanyBranchId
c.CompanyCompanyId,

FROM Company AS a INNER JOIN CompanyBranch AS c ON a.CompanyId = c.CompanyCompanyId
INNER JOIN Company AS b ON c.BranchCompanyId = b.CompanyId

Hint:this sql functions get parent company record by branch company ID.

I guess an experienced developer might help me answer the questions.

Question:
1 Since a is as same as b,why select same columns from both table?
2 What exprN means?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 20:16:56
quote:
1 Since a is as same as b,why select same columns from both table?

a is not the same as b. Yes a and b are both alias of the same table Company. The different is in the JOIN condition for the table.

Company AS a INNER JOIN CompanyBranch AS c ON a.CompanyId = c.CompanyCompanyId

a is the parent company records (c.CompanyCompanyID)

INNER JOIN Company AS b ON c.BranchCompanyId = b.CompanyId

b is the branch company records (c.BranchCompanyID)

SELECT a.CompanyId, a.AccountId, a.CompanyName, a.BusinessName AS Expr5, a.ABNNumber AS Expr6, a.IsActive AS Expr7, 
b.CompanyId AS Expr1, b.AccountId AS Expr2, b.Name AS Expr3, b.CompanyName AS Expr4, b.BusinessName, b.ABNNumber, b.IsActive,

So the select statement, the first line shows the parent company information whereas the 2nd line shows the branch company's information.
quote:
2 What exprN means?

exprN is the column alias



KH

Go to Top of Page

debradeng
Starting Member

46 Posts

Posted - 2006-10-29 : 20:34:02
Thank you Khtan,really appreciate!
Go to Top of Page
   

- Advertisement -