Posted by michaelnewport on 09/21/05 22:43

Simon Hayes wrote:
> 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

There was no error, but no rows returned.
When I used just the rsrcid then I got plenty of rows.
So on that basis I should get some rows back when using more than one
field.
Will try not exists tomorrow.

[Back to original message]