Posted by Simon Hayes on 09/21/05 18:02

What happened when you ran the code? It looks (at a very glance) as if
it should run, although it's probably not very efficient - NOT EXISTS
would most likely be a better option:

SELECT a.rsrcid,a.rsrchqnumber,c.perslastname,c.persfirstname,
b.asgtid,b.asgtactualstartdate,b.asgtactualenddate,
CASE b.enumstate
WHEN '2' THEN 'Running'
WHEN '3' THEN 'Cancelled'
WHEN '4' THEN 'Closed'
WHEN '6' THEN 'Open'
END AS status
FROM pblocal.dbo.resources a
INNER JOIN pblocal.dbo.assignments b ON b.asgtrsrcguid = a.rsrcguid
INNER JOIN pblocal.dbo.persons c ON c.persguid = a.rsrcpersguid
WHERE NOT EXISTS (
select *
from dtlocal.dbo.resources e
INNER JOIN dtlocal.dbo.assignments f ON f.asgtrsrcguid = e.rsrcguid
INNER JOIN dtlocal.dbo.users h ON h.userguid = e.rsrcuserguid
INNER JOIN dtlocal.dbo.persons g ON g.persguid = h.userpersguid
where a.rsrcid = e.rsrcid and
a.rsrchqnumber = e.rsrchqnumber and
c.perslastname = g.perslastname and
c.persfirstname = g.persfirstname)


Simon

• Next in forum: Re: Is there any way in a sproc to LOOP thru the records of a table ?
• Prev in forum: Is there any way in a sproc to LOOP thru the records of a table ?
• Thread view: Re: search on concatenated fields in sql server

[Reply to this message]