search on concatenated fields in sql server

[michaelnewport@yahoo.com]

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.rsrc hqnumber,c.pers lastname,c.pers firstname,
b.asgtid,b.asgt actualstartdate ,b.asgtactualen ddate,
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.res ources a
INNER JOIN pblocal.dbo.ass ignments b ON b.asgtrsrcguid = a.rsrcguid
INNER JOIN pblocal.dbo.per sons c ON c.persguid = a.rsrcpersguid
WHERE a.rsrcid+a.rsrc hqnumber+c.pers lastname+c.pers firstname not in
(SELECT e.rsrcid+e.rsrc hqnumber+g.pers lastname+g.pers firstname
FROM dtlocal.dbo.res ources e
INNER JOIN dtlocal.dbo.ass ignments f ON f.asgtrsrcguid = e.rsrcguid
INNER JOIN dtlocal.dbo.use rs h ON h.userguid = e.rsrcuserguid
INNER JOIN dtlocal.dbo.per sons g ON g.persguid = h.userpersguid)

[Simon Hayes]

Re: search on concatenated fields in sql server

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.rsrc hqnumber,c.pers lastname,c.pers firstname,
b.asgtid,b.asgt actualstartdate ,b.asgtactualen ddate,
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.res ources a
INNER JOIN pblocal.dbo.ass ignments b ON b.asgtrsrcguid = a.rsrcguid
INNER JOIN pblocal.dbo.per sons c ON c.persguid = a.rsrcpersguid
WHERE NOT EXISTS (
select *
from dtlocal.dbo.res ources e
INNER JOIN dtlocal.dbo.ass ignments f ON f.asgtrsrcguid = e.rsrcguid
INNER JOIN dtlocal.dbo.use rs h ON h.userguid = e.rsrcuserguid
INNER JOIN dtlocal.dbo.per sons 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

[michaelnewport@yahoo.com]

Re: search on concatenated fields in sql server


Simon Hayes wrote:[color=blue]
> 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.rsrc hqnumber,c.pers lastname,c.pers firstname,
> b.asgtid,b.asgt actualstartdate ,b.asgtactualen ddate,
> 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.res ources a
> INNER JOIN pblocal.dbo.ass ignments b ON b.asgtrsrcguid = a.rsrcguid
> INNER JOIN pblocal.dbo.per sons c ON c.persguid = a.rsrcpersguid
> WHERE NOT EXISTS (
> select *
> from dtlocal.dbo.res ources e
> INNER JOIN dtlocal.dbo.ass ignments f ON f.asgtrsrcguid = e.rsrcguid
> INNER JOIN dtlocal.dbo.use rs h ON h.userguid = e.rsrcuserguid
> INNER JOIN dtlocal.dbo.per sons 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[/color]

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.

[Simon Hayes]

Re: search on concatenated fields in sql server

If you got no rows, is it possible that your subquery result included a
NULL? NOT IN combined with a NULL returns an empty set; NOT EXISTS
returns all the non-NULL values. Google for 'sql "not in vs not
exists"' or something similar and you'll find plenty of discussions
about this.

Simon

[michaelnewport@yahoo.com]

Re: search on concatenated fields in sql server


Simon Hayes schreef:
[color=blue]
> If you got no rows, is it possible that your subquery result included a
> NULL? NOT IN combined with a NULL returns an empty set; NOT EXISTS
> returns all the non-NULL values. Google for 'sql "not in vs not
> exists"' or something similar and you'll find plenty of discussions
> about this.
>
> Simon[/color]

I owe you some beer !
I found some NULL values and some other things I didn't expect, but I'm
still digesting....

I see (from your example and my practice) that I can only do 1 INNER
JOIN statement per table and the rest of the joins on that table are
back to 'old style'. Is there a good reason for that ?

[Erland Sommarskog]

Re: search on concatenated fields in sql server

(michaelnewport @yahoo.com) writes:[color=blue]
> I see (from your example and my practice) that I can only do 1 INNER
> JOIN statement per table and the rest of the joins on that table are
> back to 'old style'. Is there a good reason for that ?[/color]

Not really sure what you mean. You can mix "old-style" and JOIN syntax
as much as you like:

FROM tbl1, tbl2,
JOIN tbl3 ON ..., tbl4
JOIN tbl5 ON ..., tbl5, tbl6

But I would not recommend that. Maybe you are thinking of:

WHERE NOT EXISTS (
select *
from dtlocal.dbo.res ources e
INNER JOIN dtlocal.dbo.ass ignments f ON f.asgtrsrcguid = e.rsrcguid
INNER JOIN dtlocal.dbo.use rs h ON h.userguid = e.rsrcuserguid
INNER JOIN dtlocal.dbo.per sons 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 )

But the WHERE clause here is a filter of the subquery that refers to
rows in the outer query, so this is not a join here.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Hayes]

Re: search on concatenated fields in sql server

michaelnewport@ yahoo.com wrote:[color=blue]
> Simon Hayes schreef:
>
>[color=green]
>>If you got no rows, is it possible that your subquery result included a
>>NULL? NOT IN combined with a NULL returns an empty set; NOT EXISTS
>>returns all the non-NULL values. Google for 'sql "not in vs not
>>exists"' or something similar and you'll find plenty of discussions
>>about this.
>>
>>Simon[/color]
>
>
> I owe you some beer !
> I found some NULL values and some other things I didn't expect, but I'm
> still digesting....
>
> I see (from your example and my practice) that I can only do 1 INNER
> JOIN statement per table and the rest of the joins on that table are
> back to 'old style'. Is there a good reason for that ?
>[/color]

The query I posted is a correlated subquery, not a join (as Erland
noted) - see "Correlated Subqueries" in Books Online. But the beer would
still be welcome :-)

Simon