How to get the matched data in two unrelated tables in SQL Server?

Question

0.00/5 (No votes)

See more:

Hello everybody,

I have two unrelated tables without any primary key or relationship as following:

SQL

Employee Table: ID, Dispaly Name, Name, Department
Participants Table: User Name, Name

This is a bad database design but because I imported both of them from Excel sheet to SQL Server Management Studio. I know there are many matches between [Display Name] and [User Name] columns in both of them, so I want to show the department column for the matches. How to do that?

I came up with this query, but it did not show me any results and I don't know why:

SQL

SELECT dbo.[Participants].[User Name], dbo.Employee.Department
    FROM 
    Employee, ['Participants']
     WHERE   Employee.[Display name] = ['Participants'].[User Name]

Posted 9-Feb-13 7:44am

matrix388

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2013-02-09T08:44:00

Solution 1

Try this:

SQL

SELECT P.[User Name], E.Department
FROM [Employee] AS E
INNER JOIN Participants AS P
ON E.[Display name] = P.[User Name]

Posted 9-Feb-13 8:44am

Kuthuparakkal

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 2 · 2013-02-09T08:53:00

['Participants'] is not the correct way to write the table name.
Instead write like [Participants] or only Participants.

So, the query will look like...

SQL

SELECT
    dbo.[Participants].[User Name],
    dbo.Employee.Department
FROM
    Employee, [Participants]
WHERE
    Employee.[Display name] = [Participants].[User Name]

Or if you will use INNER JOIN, then query will be like...

SQL

SELECT 
    Part.[User Name], 
    emp.Department
FROM 
    Employee AS emp 
INNER JOIN 
    [Participants] AS Part
ON   
    emp.[Display name] = Part.[User Name]

Thanks...