how to exclude data

[tobermory20]

Hello,

I have a statement simular to this:

SELECT     userid, last_name + ', ' + first_name AS Expr1
FROM         dbo.organizationalPerson
WHERE     (full_name LIKE '% txtLastName %')
ORDER BY last_name, first_name

My results are vary in feedback depending on the variable txtLastName. Here is my issue, I get multiple ID's back and one person could have 5 entries. All I want to return is something like A1000 and not A1000S. How can I accomplish this goal?

Regards,
~ T

 

[rcloutie]

SELECT DISTINCT ?

 

[tobermory20]

Rcloutie,

That was it sweet... thanks you

 

[tobermory20]

Okay so I was a little premature on the celebration. this is what I really was looking for:

SELECT [COLOR=red]DISTINCT SUBSTRING(userid, 1, 5)[/color] AS Expr2, last_name + ', ' + first_name AS Expr1
FROM         dbo.organizationalPerson
WHERE     (full_name LIKE '% texLastName %')

 

[tobermory20]

I am still struggling with excluding multiple instances of userids... here is my code

SELECT     [COLOR=red]userid [/color], last_name + ', ' + first_name AS Name
FROM         dbo.organizationalPerson
WHERE     (full_name LIKE '%txtLastName%')
ORDER BY last_name + ', ' + first_name

with this code I return 2 id's A1000 and A1000S I do not want A1000S to come back in my query. How can I accomplish this.

Best Regards,
~ T

 

[bborissov]

SELECT userid, MAX(last_name + ', ' + first_name) AS Name
FROM         dbo.organizationalPerson
WHERE     (full_name LIKE '%txtLastName%')
GROUP BY userid
ORDER BY last_name + ', ' + first_name

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP