Joining on sub query

[PreacherUK]

Hi Guys

I've got a query here, its returning the following error:
( I find that pasting the below query into notepad or text editor of your choice lets you see the formatting properly)

Select *
FROM dbo.tblMonthEndEstimate LEFT OUTER JOIN
(SELECT AssetClass, SecurityDescription
FROM dbo.tblGLNUtilisation
GROUP BY SecurityDescription, AssetClass, AssetClass
HAVING (AssetClass <> '000') AND (AssetClass <> '999')) ON dbo.tblMonthEndEstimate.AssetType = dbo.tblGLNUtilisation.AssetClass


Error:

Server: Msg 156, Level 15, State 1, Line 91
Incorrect syntax near the keyword 'ON'.

Am I using the sub query correctly? Do I need to use alias's

 

[dbomrrsm]

Try This

Select *
FROM dbo.tblMonthEndEstimate as A LEFT OUTER JOIN
(SELECT AssetClass, SecurityDescription
FROM dbo.tblGLNUtilisation
GROUP BY SecurityDescription, AssetClass, AssetClass
HAVING (AssetClass <> '000') AND (AssetClass <> '999')) as B
ON A.AssetType = B.AssetClass

DBomrrsm

 

[PreacherUK]

That seems to work great, I take it I needed to use the alias's yes?

 

[SQLSister]

Yes. This is a derived table and they will not work without an alias.

BTW, select * is not a good practice and should never be used when you have a join. This is because the join fields have the same data and therefore you are wasting resources providing duplicate data. Doesn't seem like much of a savings I know, but when the system is mature and has tons of records and users and is running slow, every little bit of tuning can help. So it is better by far to always write the SQL with efficiency in mind rather than have to tweak everything later. Right now I'm inthe prpocess of fixing some of the less efficient code we wrote way back when we started the project before any of us knew about efficency and it is a slow and miserable process. So the sooner you start thinking about these issues and writing code designed to be efficient, the less trouble you will have later on.

 

[PreacherUK]

In my defence the SELECT * was just while I was testing/messing about I was building the query to be an update query and as such doesnt use SELECT *. But the advice is well taken and something I'll bear in mind

Thank you both.