Link to home
Start Free TrialLog in
Avatar of cdemott33
cdemott33Flag for United States of America

asked on

T-SQL query writing help need. DISTINCT or JOIN or something else?

Could someone help me with a query.  I have two tables.  One is "Users" and the other is "Visited" table.   The "Visited" table has a Foreign Key to the User table (See attached Picture) via the User ID.   I need to return the first and last name combined along with the company column where there is a match to the UserID in the Visited table.  I don't want duplicates.  I want unique (or Distinct) matches found in the Visited table.

User generated image


How would I do this?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

SELECT DISTINCT COALESCE(First, '') + ' ' + COALESCE(Last, '') as Name, Company
FROM Users
WHERE UserID IN (SELECT UserID FROM Visited)
ASKER CERTIFIED SOLUTION
Avatar of cdemott33
cdemott33
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Define 'can't seam to get it to work', preferably with some kind of error message.
@cdemott33
indeed an error-message on jimhorn's solution would be appropriate

What jimhorn's solution has 2 advantages
- if first or last-name are null it still returns the part that is there, your solution returns NULL
- you don't use anything of the table visited so a join is not necessary the IN will do, but EXISTS will even be better and then you don't need a group by
Avatar of cdemott33

ASKER

Works!