Link to home
Create AccountLog in
Avatar of markej
markejFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Querying a combined field

I have a TSQL stored procedure that originally checked for firstname or lastname I need to now check for a combined value ie insted of 'Ian' or 'Brook' I now need to find 'Ian Brooks',
I haven't got a clue how to do this so HELP.

I've included my current stored procedure.

Thanks

Mark

      SELECT Userinfo.tel, Userinfo.email, Userinfo.CompanyName, ISNULL(AVG(PropertyReview.Rating), 0) AS rating,
      Userinfo.userid, Userinfo.fname, Userinfo.lname,
      Userinfo.Logo, Userinfo.AboutDescription, Userinfo.accType
      FROM Userinfo LEFT OUTER JOIN
      PropertyReview ON Userinfo.userid = PropertyReview.Landlordid
      GROUP BY Userinfo.accType, Userinfo.tel, Userinfo.email, Userinfo.CompanyName, Userinfo.userid, Userinfo.fname,
      Userinfo.lname, Userinfo.Logo, Userinfo.AboutDescription
      HAVING (Userinfo.accType = 2)
      AND (Userinfo.CompanyName LIKE @agent + '%' OR  Userinfo.fname LIKE @agent + N'%' OR Userinfo.lname LIKE @agent + N'%')
Avatar of Vel Eous
Vel Eous

Assuming your firstname and lastname fields are strings, then you could concat the two fields together to create a combined field, example below:

SELECT firstname + ' ' + lastname AS Combined FROM table;

Open in new window

-- User CTE to first do the grouping of Property - If earlier version of SQL Server replace CTE with temp table
-- but the concept will be similar
-- I am guessing that there is a sinlge record for each user in the user table so it is better to first to
-- the grouping on property and then when you have a sinle grouped property record per user join on this
-- single userid to get the user info
; With CTE_Property_User (LandLordID, rating)
AS
(
   SELECT LandLordID
   ,ISNULL(AVG(PropertyReview.Rating), 0) AS rating
   GROUP BY LandLordID
)
SELECT
u.tel
,u.email
,u.CompanyName
,p.rating
,u.userid
,u.fname
,u.lname
,u.fname + ' ' + u.lname AS [FullName]
FROM CTE_Property_User p
-- Join grouped records out to the user table
JOIN Userinfo u ON u.userid = p.LandLordID
-- then do search criteria - (u.fname + ' ' + u.lname) expression gives the full name
WHERE
(u.accType = 2)
AND
(u.CompanyName LIKE @agent + '%' OR (u.fname + ' ' + u.lname) LIKE @agent + '%')
Avatar of markej

ASKER

Thanks for the suggestions BCUNNEY But how do I add this to my stored procedure? I tried adding that to my stored procedure below this part
"@agent as nvarchar(50),
@type as int

AS

if @type = 2
begin"

and keep getting the following errors when I try to save :
"Invalid column name 'landlordID' "
"Invalid column name 'landlordID' "
"The multi-part identifier "PropertyReview.Rating" could not be bound. "

What am I doing wrong?
Avatar of PortletPaul
"I've included my current stored procedure." - where?

seems you know how to validate each piece individually, but not together? am I understanding correctly (something like  (Firstname= 'Ian' and Lastname='Brooks')) - i.e. why would a concatenation assist?

is it due to a change from storing the pieces, but now store the whole?
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of markej

ASKER

PortletPaul here is my stored procedure
@agent as nvarchar(50),
@type as int

AS

if @type = 2
begin
      SELECT        Userinfo.tel, Userinfo.email, Userinfo.CompanyName, ISNULL(AVG(PropertyReview.Rating), 0) AS rating, Userinfo.userid, Userinfo.fname, Userinfo.lname,
                               Userinfo.Logo, Userinfo.AboutDescription, Userinfo.accType
      FROM            Userinfo LEFT OUTER JOIN
                               PropertyReview ON Userinfo.userid = PropertyReview.Landlordid
      GROUP BY Userinfo.accType, Userinfo.tel, Userinfo.email, Userinfo.CompanyName, Userinfo.userid, Userinfo.fname, Userinfo.lname, Userinfo.Logo,
                               Userinfo.AboutDescription
      HAVING        (Userinfo.accType = 2) AND (Userinfo.CompanyName LIKE @agent + '%' OR
                               Userinfo.fname LIKE @agent + N'%' OR
                               Userinfo.lname LIKE @agent + N'%')`
end

if @type = 3
begin

--SAME AS ABOVE JUST MAPS DIFFERENT FIELD TO COMPANYNAME

end

RETURN
Avatar of markej

ASKER

BCUNNEY, I get the following error
Executed SQL statement: SELECT u.tel, u.email, u.CompanyName, p.rating, u.userid, u.fname, u.lname, u.fname + ' ' + u.lname AS FullName, p.* FROM CTE_Property_User AS p INNER JOIN Userinfo AS u ON u.userid = p.LandLordID WHERE (u.accType = 2) AND (u.CompanyName LIKE @agent ...
Error Source: .Net SqlClient Data Provider
Error Message: Invalid object name 'CTE_Property_User'.

When I run the stored procedure although it saves OK, Is this due to version?
Avatar of markej

ASKER

Correction I don't get the error Here is the stored procedure
@agent as nvarchar(50),
@type as int

AS

if @type = 2
begin

;With CTE_Property_User (LandLordID, rating)
AS
(
   SELECT LandLordID
   ,ISNULL(AVG(PropertyReview.Rating), 0) AS rating
   FROM PropertyReview
   GROUP BY LandLordID
)
SELECT
u.tel
,u.email
,u.CompanyName
,p.rating
,u.userid
,u.fname
,u.lname
,u.fname + ' ' + u.lname AS [FullName]
FROM CTE_Property_User p
-- Join grouped records out to the user table
JOIN Userinfo u ON u.userid = p.LandLordID
-- then do search criteria - (u.fname + ' ' + u.lname) expression gives the full name
WHERE
(u.accType = 2)
AND
(u.CompanyName LIKE @agent + '%' OR (u.fname + ' ' + u.lname) LIKE @agent + '%')
end

when I enter 'ian'

I expect to get the record for Ian Brooks returned and I get no records
the followinf test SQL
SELECT * FROM UserInfo where fname like 'IAN%'

and take note of the value in the accType column
Avatar of markej

ASKER

Gives a value of 2, my current stored procedure will also return the value if I enter Ian OR Brooks
Try the following test SQL

SELECT
CASE
   WHEN u.fname + ' ' + u.lname = 'Ian Brooks' Then 1
   WHEN u.fname + ' ' + u.lname like 'Ian Brooks%' Then 1
   ELSE 0
END MatchChecking
,*
FROM UserInfo where fname like 'IAN%'
or

SELECT
CASE
   WHEN u.fname + ' ' + u.lname = 'Ian Brooks' Then 1
   WHEN u.fname + ' ' + u.lname like 'Ian Brooks%' Then 2
   ELSE 0
END MatchChecking
,*
FROM UserInfo where fname like 'IAN%'
Avatar of markej

ASKER

Using your 2nd script I get
1      33      Ian      Brooks      123456789      ian-brooks@email      2      brooks70      test      1      test Co      Aliquam erat volutpat. Cras eu pulvinar sapien. Pellentesque in dui ut neque fermentum posuere. Vestibulum aliquam ultricies scelerisque. Duis non neque a massa cursus posuere. Proin porttitor ligula ac nulla placerat tristique. Vestibulum sed augue leo, dapibus sagittis nisi. Cras imperdiet sem quis lectus bibendum gravida.
Try the following version of proc and pass in ian

if @type = 2
begin

;With CTE_Property_User (LandLordID, rating)
AS
(
   SELECT LandLordID
   ,ISNULL(AVG(PropertyReview.Rating), 0) AS rating
   FROM PropertyReview
   GROUP BY LandLordID
)
SELECT
u.tel
,u.email
,u.CompanyName
,p.rating
,u.userid
,u.fname
,u.lname
,u.fname + ' ' + u.lname AS [FullName]
FROM CTE_Property_User p
-- Join grouped records out to the user table
JOIN Userinfo u ON u.userid = p.LandLordID
-- then do search criteria - (u.fname + ' ' + u.lname) expression gives the full name
WHERE
(u.accType = 2)
AND
((u.fname + ' ' + u.lname) LIKE @agent + '%')
end
select ...
where charindex(fname,fullname) > 0 and charindex(lname,fullname) > 0
Can you supply some sample data and the expected output?
Avatar of markej

ASKER

My last comments about lack of data were due to client deleting records and me not checking. Useful learning curve.