Subquery - SQL Server Forums

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

Transact-SQL (2000)

Subquery

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-11-21 : 16:28:30

I'm a newbie so I hope this isn't too dumb, but I'm having problems with this subquery. I don't know how to alias the nested part of this.

SELECT *
FROM ( SELECT BAStatus, Ticker, CEOFName, CEOLName, CompanyName FROM Relationships
INNER JOIN TCompanies ON Relationships.CompID = TCompanies.CompID
WHERE TCompanies.CompID=13844 ) As tblTemp
--error is here
WHERE (Tcompanies.Exchange) IN ('AMEX')

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Tcompanies' does not match with a table name or alias name used in the query.

*****************************************

SELECT *
FROM ( SELECT BAStatus, Ticker, CEOFName, CEOLName, CompanyName FROM Relationships
INNER JOIN TCompanies ON Relationships.CompID = TCompanies.CompID
WHERE TCompanies.CompID=13844 ) As tblTemp
--error is here
WHERE (tblTemp.Exchange) IN ('AMEX')

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Exchange'.

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-11-21 : 17:02:45

SELECT *
FROM ( SELECT BAStatus, Ticker, CEOFName, CEOLName, CompanyName FROM Relationships
INNER JOIN TCompanies ON Relationships.CompID = TCompanies.CompID
WHERE TCompanies.CompID=13844 and (Tcompanies.Exchange) IN ('AMEX') ) As tblTemp

With Regards
BSR

evanburen
Posting Yak Master

167 Posts

Posted - 2005-11-21 : 21:52:35

Thanks for the reply. Is it possible to also add an ORDER BY clause to this?

SELECT *
FROM ( SELECT BAStatus, Ticker, CEOFName, CEOLName, CompanyName FROM Relationships
INNER JOIN TCompanies ON Relationships.CompID = TCompanies.CompID
WHERE TCompanies.CompID=13844 and (Tcompanies.Exchange) IN ('AMEX')) As tblTemp
ORDER BY TCompanies.CompanyName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-21 : 22:11:47

it should be

SELECT *
FROM ( SELECT BAStatus, Ticker, CEOFName, CEOLName, CompanyName FROM Relationships
INNER JOIN TCompanies ON Relationships.CompID = TCompanies.CompID
WHERE TCompanies.CompID=13844 and (Tcompanies.Exchange) IN ('AMEX')) As tblTemp
ORDER BY tblTemp.CompanyName

actually you can do this ..

SELECT BAStatus, Ticker, CEOFName, CEOLName, CompanyName FROM Relationships
INNER JOIN TCompanies ON Relationships.CompID = TCompanies.CompID
WHERE TCompanies.CompID=13844 and (Tcompanies.Exchange) IN ('AMEX')
ORDER BY TCompanies.CompanyName

[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources