Subqueries - 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)

Subqueries

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-11-27 : 14:26:46

Hi,

I was wondering if the following is possible and if not, then how to get around it:
I have a main query which return a JOB NUMBER from a table X and I want to apply an outer join from a subquery that returns the total number of hours worked for that job. The problem is that part of the subquery need to use the JOBNO as a filter:


select jomast.fjobno...
from jomast....
left outer join 
(
SELECT substring(ladetail.fjobno,1,5)+'-0000' as job ,Sum(datediff(minute,fsdatetime,fedatetime)) AS minutes
FROM ladetail 
WHERE (ladetail.FCDEPT<>'50') AND (ladetail.fjobno>'10000') 
and ladetail.fjobno LIKE SUBSTRING(jomast.fjobno,1,5)+'%' 
group by substring(ladetail.fjobno,1,5)
) AS F on jomast.fjobno = F.job 
)

so essentially it is like passing a parameter to the subquery.... How can I do this legally?
the message I get now is:
The column prefix 'jomast' does not match with a table name or alias name used in the query.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-27 : 14:55:16

You need to use a correlated subquery for that - which means the subquery must run for each row in the main query and you can't do that with a subquery that is used a derived table (which is what you are doing).

You need to either put the subquery in the select list and just have it return the value, something like

select jomast.fjobno, (subquery)
from jomast....

or you can probably do what you want without using a subquery at all like this (I don't know for sure because you used ... in your query and what you have there will affect this).

select jomast.fjobno, Sum(datediff(minute,fsdatetime,fedatetime)) AS minutes
from jomast
left outer join ladetail ON jomast.fjobno = substring(ladetail.fjobno,1,5)+'-0000'
WHERE (ladetail.FCDEPT<>'50') AND (ladetail.fjobno>'10000') 
group by jomast.fjobno

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-11-27 : 15:02:47

never mind... I realized that I had a skewed way of thinking about this one. I have figured it out now. Thanx anyway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources