Avatar of cdemott33
cdemott33
Flag 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.

SQL Needed Result


How would I do this?
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
cdemott33

8/22/2022 - Mon
Jim Horn

SELECT DISTINCT COALESCE(First, '') + ' ' + COALESCE(Last, '') as Name, Company
FROM Users
WHERE UserID IN (SELECT UserID FROM Visited)
ASKER CERTIFIED SOLUTION
cdemott33

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Horn

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

@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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
cdemott33

ASKER
Works!