How to join 2 tables with many-to-one relationship

Transact-SQL
1

My first table is a customer table with the following schema:

CustomerTable
CustomerEmail CustomerName SalespersonID
lee@email.com Lee 101
john@email.com John 102
mike@email.com Mike 101
jimmy@email.com Jimmy 104
jack@email.com Jack 105
cam@email.com Cam 105

In the customer table - CustomerEmail is the PrimaryKey

In customer table, many customers may be assigned to same salesforce ID

Second table is for Salespersons

SalespersonTable
SalespersonID SalespersonEmail
101 sp1@domain.com
102 sp2@domain.com
103 sp3@domain.com
104 sp4@domain.com

In the salesperson table - SalespersonID is the PrimaryKey

Expected output is something like this:

CustomerEmail CustomerName SalespersonID SalespersonEmail
lee@email.com Lee 101 sp1@domain.com
john@email.com John 102 sp2@domain.com
mike@email.com Mike 101 sp1@domain.com
jimmy@email.com Jimmy 104 sp4@domain.com

This is what I have tried so far.

SELECT
a.CustomerEmail,
a.CustomerName,
a.SalespersonID,
b.SalespersonEmail

FROM CustomerTable a
LEFT JOIN SalespersonTable b
ON a.SalespersonID = b.SalespersonID

AND

SELECT
a.CustomerEmail,
a.CustomerName,
a.SalespersonID,
b.SalespersonEmail

FROM CustomerTable a
INNER JOIN SalespersonTable b
ON a.SalespersonID = b.SalespersonID

I have tried LEFT JOIN and INNER JOIN but both are not producing correct results.

Can anyone please guide?

2

For the sample data that you have shown, INNER JOIN will gives you the expected result.

3

The INNER JOIN query above should work fine. What about the result is not what you expect?

4

add condition AND a.CustomerName = a.CustomerName to the INNER JOIN query hope this helps.

5

Hi @v8553

Thanks for your help, just for my understanding what AND a.CustomerName = a.CustomerName will do by adding in the inner join? Does that mean we I want additional columns fill Customer table, I'll be to add in SELECT & INNER JOIN, both?

6

SELECT
a.CustomerEmail,
a.CustomerName,
a.SalespersonID,
b.SalespersonEmail

FROM CustomerTable a
INNER JOIN SalespersonTable b
ON a.SalespersonID = b.SalespersonID
AND a.CustomerName = a.CustomerName

7

Thank @v8553 for the updated query.

Could you please help me understand what CustomerName is doing in the join in this case?

8

its filtering out which has same customerName

9

hi

hope this helps

its doing what you are expecting

create data script

declare @CustomerTable table (CustomerEmail varchar(20), CustomerName varchar(20) , SalespersonID int)
insert into @CustomerTable Values
('lee@email.com','Lee', 101)
,('john@email.com','John', 102)
,('mike@email.com','Mike', 101)
,('jimmy@email.com','Jimmy', 104)
,('jack@email.com','Jack', 105)
,('cam@email.com','Cam', 105)

declare @SalespersonTable table (SalespersonID int , SalespersonEmail varchar(20))
insert into @SalespersonTable Values
(101,'sp1@domain.com')
,(102,'sp2@domain.com')
,(103,'sp3@domain.com')
,(104,'sp4@domain.com')

SELECT
   a.CustomerEmail
 , a.CustomerName
 , a.SalespersonID
 , b.SalespersonEmail
FROM 
    @CustomerTable a
       LEFT JOIN 
	@SalespersonTable b
              ON a.SalespersonID = b.SalespersonID

image
image1022×761 24.1 KB