Microsoft SQL Server forum - search on concatenated fields in sql server

Author

search on concatenated fields in sql server

michaelnewport@yahoo.com

2005-09-21, 9:24 am

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)

Simon Hayes

2005-09-21, 11:24 am

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

michaelnewport@yahoo.com

2005-09-21, 8:23 pm

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.

Simon Hayes

2005-09-22, 3:23 am

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

2005-09-22, 11:24 am

Simon Hayes schreef:

> 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

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

2005-09-22, 8:24 pm

(michaelnewport@yaho
o.com) writes:
> 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 ?

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.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)

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@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Simon Hayes

2005-09-22, 8:24 pm

michaelnewport@yahoo
.com wrote:
> Simon Hayes schreef:
>
>
>
>
> 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 ?
>

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

Sponsored Links