procedure formula or nested select 1

[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
????????
)

In the Ch table I obtain:
JNr Type
24 Cust
24 A
24 A
25 A
25 A
25 A
25 A
26 Cust
27 A
27 A


I should eliminate all records(jobs) that have Cust between them, so I should eliminate:
24 Cust
24 A
24 A
26 Cust
And should remain:
25 A
25 A
25 A
25 A
27 A
27 A


And, as well, how do I print:
Substring(Ch.Cost,4,7) JNr
Ch.Type

Any idea is very welcome

Thank you

 

[r937]

SELECT Customers.SalesRep
     , Jobs.JNr
     , Jobs.JNr
     , Mov.Type
     , Mov.Date
     , [red]Ch.Type[/red]
  FROM Customers
INNER 
  JOIN Jobs 
    on Jobs.Acc = Customers.Acc
   AND Jobs.JNr NOT IN ('54','49')
INNER 
  JOIN JobsMov 
    on JobsMov.JNr = Jobs.JNr
INNER 
  JOIN Mov 
    on Mov.MNr = JobsMov.MNr
   AND Mov.Type <> 'imp'
[red]LEFT OUTER
  JOIN ( SELECT Substring(Cost,4,7) AS JNr
           FROM Ch
          WHERE Type = 'Cust' ) AS not_these 
    ON not_these.JNr = Jobs.JNr
LEFT OUTER
  JOIN Ch
    ON Substring(Ch.Cost,4,7) = Jobs.JNr[/red]
 WHERE Customers.SalesRep = 'Ara'
   AND [red]not_these.JNr IS NULL[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[codrutza]

It's working, thank you Rudy.

Can you explain, please, in detail, what this does?
LEFT OUTER JOIN
( SELECT Substring(Cost,4,7) AS JNr FROM Ch WHERE Type = 'Cust' ) AS not_these
ON not_these.JNr = Jobs.JNr
LEFT OUTER JOIN
Ch ON Substring(Ch.Cost,4,7) = Jobs.JNr WHERE Customers.SalesRep = 'Ara' AND not_these.JNr IS NULL

Thanks

 

[r937]

do you understand how LEFT OUTER JOIN along with WHERE ... IS NULL works?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[codrutza]

What I understand:
First selects into table not_these the records which are not cust
Second selects not_these.JNr IS NULL
I do understand left outer, but not in this context.
left outer is when you have 2 tables customers orders and you link them with left outer join by let's say c_id and you obtain customers that have orders+customers that don't have orders.
I am very beginner in stored procedures. I'm sorry

 

[r937]

okay, you have the basic idea

Jobs is joined to not_these with a LEFT OUTER JOIN, so all Jobs rows will be returned, along with matching not_these rows, if any

but then the WHERE clause says [red]not_these.JNr IS NULL[/red]

this means only Jobs without matching not_these jobs are returned

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

p.s. yes, we have no bananas ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[codrutza]

It's very good, but I still don't get how come I eliminate all records(jobs)that have Cust between them, so I eliminate:
24 Cust
24 A
24 A
26 Cust
and not only
24 Cust
24 Cust

 

[r937]

i'm sorry, i don't understand your last question

does the query i provided work correctly or not?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[codrutza]

yes, it's correct