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

cdemott33
cdemott33 used Ask the Experts™
on
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.

SQL Needed Result


How would I do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
SELECT DISTINCT COALESCE(First, '') + ' ' + COALESCE(Last, '') as Name, Company
FROM Users
WHERE UserID IN (SELECT UserID FROM Visited)
Hi jim.  Thanks for the code but I can't seam to get it to work.

After digging around I was able to write this block of code which seams to work perfectly.

SELECT  u.First + ' ' + u.Last, u.Company

FROM Users u, Visited v,

WHERE v.UserID = u.UserID

GROUP BY u.First + “ “ + u.Last, u.Company

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Define 'can't seam to get it to work', preferably with some kind of error message.

Commented:
@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

Author

Commented:
Works!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial