Last post Aug 19, 2015 03:17 PM by Gayatry
Aug 15, 2015 09:37 AM|Bobby-Z|LINK
SELECT DISTINCT(SUBSTRING(Users.LastName,1,1)) AS LastName, CuAcctsUsr.UserId FROM CuAcctsUsr INNER JOIN Users ON CuAcctsUsr.UserId = Users.UserId
I am trying to get a distinct list of first letter of last names, it works when I use a single table, but mixing tables - If I have seven people whose lasts names begin with L it shows the letter L seven times.
Aug 15, 2015 01:27 PM|mbanavige|LINK
Distinct is applied across the entire result set (all the result columns).
so if there are users in the result with different userid's, then they appear as separate distinct rows of data.
if you drop CuAcctsUsr.UserId from your select, then you should see a different result.
Aug 18, 2015 12:46 AM|Bobby-Z|LINK
That presents a problem"
A. I have several user tables
1. Users (general info) (first name Last Name, Phone, email) etc
2. CuAcctUsers - Users who are customers
3. HrUsers - Users who are employees
4. VndrUsers - Users who are vendors
If I drop the users table, I get all users even those who are not customers
I use the userId in ButtonArgument for managing the users profile
Is there a workaround? or do i need to add a column to each table using lastName for each of the tables? I wanted to avoid duplicate, or having to simply program the whole alphabet as filters even when some letters aren't used.
Aug 18, 2015 07:28 PM|mbanavige|LINK
Is there a workaround? or do i need to add a column to each table using lastName for each of the tables?
Given that you are using the userid in the data returned, i'm not seeing how that would help.
imagine two users
You seem to want "S" only once but you want both userid's 1 and 2
It sounds like you might be trying to use 1 query to fulfill two different purposes - which might require two different queries.
Aug 19, 2015 03:17 PM|Gayatry|LINK
what ever the query you have join make it as inner query. in the outer query make distinct
select distinct lastName,Userid
SUBSTRING(Users.LastName,1,1) AS LastName, CuAcctsUsr.UserId
INNER JOIN Users ON CuAcctsUsr.UserId = Users.UserId) as g1