By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

search on concatenated fields in sql server

P: n/a
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.persfirst name,
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.persfirst name not in
(SELECT e.rsrcid+e.rsrchqnumber+g.perslastname+g.persfirst name
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)

Sep 21 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.persfirst name,
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

Sep 21 '05 #2

P: n/a

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.persfirst name,
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.

Sep 21 '05 #3

P: n/a
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

Sep 22 '05 #4

P: n/a

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 ?

Sep 22 '05 #5

P: n/a
(mi************@yahoo.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, es****@sommarskog.se

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

Sep 22 '05 #6

P: n/a
mi************@yahoo.com wrote:
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 ?


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
Sep 22 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.