May 28, 2009 at 10:01 am
Given tables-
Client
ClientId (IDENTITY)
LastName
FirstName
MiddleName
ClientAccount
AccountId (IDENTITY)
ClientId (FK)
AccountNumber
I need to write a query that returns a list of Client Ids that match ALL search terms entered by a user. Search terms are entered into one text box, and for simplicity's sake we'll say can include Name or AccountNumber. An example might look like 'John A78389S87 Smith'. Although it is visually obvious that 'A78389S87' is probably an Account Number, programmatically I have no good way of knowing this.
Given that
searchTerm1 = 'John'
searchTerm2 = 'A78389S87'
searchTerm3 = 'Smith'
and given that the number of search terms is variable, how do I gracefully accomplish something like the following?
SELECT c.ClientId
FROM Client c
JOIN ClientAccount ca ON c.ClientId = ca.ClientId
WHERE (searchTerm1 = c.LastName
OR searchTerm1 = c.FirstName
OR searchTerm1 = c.MiddleName
OR searchTerm1 = ca.AccountNumber)
AND (searchTerm2 = c.LastName
OR searchTerm2 = c.FirstName
OR searchTerm2 = c.MiddleName
OR searchTerm2 = ca.AccountNumber)
AND (searchTerm3 = c.LastName
OR searchTerm3 = c.FirstName
OR searchTerm3 = c.MiddleName
OR searchTerm3 = ca.AccountNumber)
May 28, 2009 at 10:12 am
would it not be possible to enter you various search items into a temp table/table variable with 1 col and do various left joins onto your main table and take results that are not null as matches?
How does that sound?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 28, 2009 at 10:22 am
something like this:
CREATE TABLE Client
(ClientId INT IDENTITY(1,1),
LastName VARCHAR(100),
FirstName VARCHAR(100),
MiddleName VARCHAR(100))
CREATE TABLE ClientAccount
(AccountId INT IDENTITY(1,1),
ClientId INT,
AccountNumber VARCHAR(100))
--TEST DATA
INSERT INTO Client
SELECT 'Stobbs', 'Chris' , 'Terence' UNION ALL
SELECT 'Joe', 'Blogs' , 'Middle' UNION ALL
SELECT 'Jane', 'Doe' , 'Middle'
INSERT INTO ClientAccount
SELECT 1, 'A12313121' UNION ALL
SELECT 2, 'B12313321' UNION ALL
SELECT 3, 'C12313991'
--CODE TO SEARCH
DECLARE @SearchString VARCHAR(MAX)
DECLARE @Delimiter VARCHAR(10)
SET @Delimiter = ' '
SET @SearchString = 'Middle A12313121'
DECLARE @Search TABLE
(Val VARCHAR(100))
INSERT INTO @Search
SELECT SUBSTRING(@SearchString+@Delimiter, n,
CHARINDEX(@Delimiter, @SearchString+@Delimiter, n) - n)
FROM tally
WHERE n <= LEN(@SearchString)
AND SUBSTRING(@Delimiter + @SearchString,
n, 1) = @Delimiter
ORDER BY n
SELECT * FROM @Search
SELECT *
FROM Client cl
INNER JOIN ClientAccount ca ON cl.ClientId = ca.ClientId
LEFT JOIN @Search a ON a.val = cl.LastName
LEFT JOIN @Search b ON b.val = cl.FirstName
LEFT JOIN @Search c ON c.val = cl.MiddleName
LEFT JOIN @Search d ON d.val = ca.AccountNumber
WHERE COALESCE(a.val,b.val,c.val,d.val) IS NOT NULL
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2009 at 9:17 am
Thanks for your reply Chris. It feels close but seems to give results where ANY of the search terms match instead of ALL. I'll keep working on it....
May 29, 2009 at 9:21 am
ah sorry I must have miss read the spec...
if you need more help just shout
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2009 at 6:28 pm
suggestion:
alter function dbo.fParameterMatrix(
@parm1 varchar(64), @parm2 varchar(64), @parm3 varchar(64), @parm4 varchar(64)
)
returns @matrix table
(
firstName varchar(64),
lastName varchar(64),
accountNumber varchar(64),
otherField varchar(64),
numFields as isnull(sign(len(lastName)),0)+
isnull(sign(len(firstName)),0)+
isnull(sign(len(accountNumber)),0)+
isnull(sign(len(otherField)),0),
duplicateFields as case when lastName in (firstName,accountNumber,otherField)
or firstName in (accountNumber,otherField)
or accountNumber in (otherField) then 'Y' end
)
as
begin
declare @parms table (parm varchar(64) null)
insert @parms
select @parm1
union
select @parm2
union
select @parm3
union
select @parm4
insert into @matrix (firstName, lastName, accountNumber, otherField)
select T1.parm as firstName,
T2.parm as lastName,
T3.parm as accountNumber,
T4.parm as otherField
from
@parms T1
cross join @parms T2
cross join @parms T3
cross join @parms T4
delete @matrix
where duplicateFields = 'Y'
or numFields !=
(select count(*) from @parms where parm is not null)
return
end
this function creates a matrix of all parameters in all 'fields'. this example:
select firstName, lastName, accountNumber, otherField
from dbo.fParameterMatrix( 'Sally', 'Smith', 'b-24005', null)
returns this matrix:
firstName lastName accountNumber otherField
b-24005 NULL Smith Sally
b-24005 NULL Sally Smith
Sally NULL Smith b-24005
...
b-24005 Smith NULL Sally
Sally Smith b-24005 NULL
Sally Smith NULL b-24005
now, just join the matrix to your tables like so (I forgot you were using [middleName]... I just used [otherField]):
SELECT c.ClientId
FROM Client c
JOIN ClientAccount ca ON c.ClientId = ca.ClientId
JOIN dbo.fParameterMatrix('Sally', 'Smith', 'b-24005', null) P
ON c.firstName = isnull(P.firstName,c.firstName)
and c.lastName = isnull(P.lastName,c.lastName)
and c.accountNumber = isnull(P.accountNumber,c.accountNumber)
and c.middleName = isnull(P.otherField,c.middleName)
P.S.: if the columns you're using for the search aren't indexed, such as join could be very resource intensive.
P.P.S.: you could also join character columns using LIKE and wildcards
May 30, 2009 at 2:44 am
According to example given by Christopher Stobbs, can you do something like this?
CREATE TABLE Client
(ClientId INT IDENTITY(1,1),
LastName VARCHAR(100),
FirstName VARCHAR(100),
MiddleName VARCHAR(100))
CREATE TABLE ClientAccount
(AccountId INT IDENTITY(1,1),
ClientId INT,
AccountNumber VARCHAR(100))
--TEST DATA
INSERT INTO Client
SELECT 'Stobbs', 'Chris' , 'Terence' UNION ALL
SELECT 'Joe', 'Blogs' , 'Middle' UNION ALL
SELECT 'Jane', 'Doe' , 'Middle'
INSERT INTO ClientAccount
SELECT 1, 'A12313121' UNION ALL
SELECT 2, 'B12313321' UNION ALL
SELECT 3, 'C12313991'
SELECT * FROM Client
SELECT * FROM ClientAccount
declare @tbl table(searchData varchar(100))
insert into @tbl
select 'Stobbs' union all
select 'A12313121' union all
select 'Blogs'
select * from Client c1 join ClientAccount c2 on c1.ClientID=c2.ClientID and
(c1.LastName in (select searchData from @tbl) or
c1.FirstName in (select searchData from @tbl) or
c2.AccountNumber in (select searchData from @tbl))
"Don't limit your challenges, challenge your limits"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply