select nested aggregate

[codrutza]

SQL Server 2000

I have to write a stored procedure:

SELECT
Customers.SalesRep,
Jobs.JNr,
Jobs.JNr,
Mov.Type,
Mov.Date

FROM

Customers
INNER JOIN Jobs on Customers.Acc=Jobs.Acc
INNER JOIN JobsMov on Jobs.JNr=JobsMov.JNr
INNER JOIN Mov on JobsMov.MNr=Mov.MNr

WHERE
Customers.SalesRep=”Ara”
AND Jobs.JNr not in (‘54’,’49’)
AND Mov.Type<>’imp’

AND Jobs.JNr in
(
SELECT
FROM Ch
WHERE
Substring(Ch.Cost,4,7)=Jobs.JNr
AND
Ch.Type<>’Cust’
)

But the nested select is obviously not correct, because in the Ch table I have
24 Cust
24 A
24 A
25 A
25 A
25 A
25 A

I should eliminate all records that have the JNr 24, but not 25. (I eliminate only the record 24 Cust which is not correct because JNr 24 shouldn’t appear at all in report); that’s why I am thinking at aggregates on JNr, but I don’t know how to write that?

Any idea is very welcome

Thank you

 

[PWise]

Try

Select ......
From Customers
INNER JOIN Jobs on Customers.Acc=Jobs.Acc
INNER JOIN JobsMov on Jobs.JNr=JobsMov.JNr
INNER JOIN Mov on JobsMov.MNr=Mov.MNr
And Customers.SalesRep="Ara"
--AND Jobs.JNr not in ('54','49')
AND Mov.Type<>'imp'
inner join (SELECT Substring(Ch.Cost,4,7) JNr
            FROM Ch 
            WHERE Ch.Type<>'Cust'
            )ch
on cn.jnr==Jobs.JNr

 

[codrutza]

Thank you

However, maybe I didn't say it right.
I don't make any sense from that:

inner join (SELECT Substring(Ch.Cost,4,7) JNr FROM Ch WHERE Ch.Type<>'Cust')ch
on cn.jnr==Jobs.JNr

1. cn.jnr means ch.jnr?
2. that is a comment --AND Jobs.JNr not in ('54','49')? I need to exclude some jobs
3. how does this eliminate all the records
24 Cust
24 A
24 A
?
I don't need to eliminate just the record
24 Cust
but All the records for a JNr -24- which has the Cust between them, so all 3 records

 

[PWise]

how about this

Select......
FROM         Customers
INNER JOIN  Jobs 
ON Customers.Acc = Jobs.Acc
INNER JOIN  JobsMov 
ON Jobs.JNr = JobsMov.JNr
INNER JOIN Mov 
ON JobsMov.MNr = Mov.MNr
And Customers.SalesRep = [Ara]
AND Jobs.JNr not in ('54','49')
AND Mov.Type<>'imp'
left join (SELECT distinct Substring(Ch.Cost,4,7) JNr 
           FROM Ch            
           WHERE Ch.Type<>'Cust'            
            )ch
on cn.jnr=Jobs.JNr
where cn.jnr is null

 

[codrutza]

Hello

I am new to stored procedures so if you don't mind, explain this:

left join
(SELECT distinct Substring(Ch.Cost,4,7) JNr
FROM Ch
WHERE Ch.Type<>'Cust')ch
on cn.jnr=Jobs.JNrwhere cn.jnr is null

in special, what table is cn?

Thank you

 

[PWise]

Sorry that is a typo it should read

on ch.jnr=Jobs.JNrwhere cn.jnr is null

 

[BrendanBlackford]

Thank you.

Can you explain this? It doesn't work.
25 A
25 A
25 A
25 A
dissapeared as well.
The only thing that appear is the jobs that don't have correspondent in ch.


How do I print that:
Substring(Ch.Cost,4,7) JNr
Ch.Type

I appear now with the name BB, because I'm working on other computer.