Posted by michaelnewport on 09/21/05 15:24

Greetings,

I used to do this kind of query in Ingres, where I concatenate
(+)various fields as one field and search accordingly.
Is there an equivalent method in SQL server ?

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 a.rsrcid+a.rsrchqnumber+c.perslastname+c.persfirstname not in
(SELECT e.rsrcid+e.rsrchqnumber+g.perslastname+g.persfirstname
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)

[Back to original message]