How to join with one key but multiple results?

[fereze]

Hi I have a table similar to this:

Brand ID_EMP

Nike 123

Adidas 456

Lotto 123

other table like this:

code name

123 Smith

456 Myers

The result should be

Nike 123 Smith 

Adidas 456 Myers 

Lotto 123 Smith

but insted of this I'm getting:

Nike 123 Smith john

adidas 456 Myers. Mike

This is the query that I'm using.

| dbxquery query="SELECT * FROM Clients ;" connection="Clients"
| where NOT like(SAFE_NAME,"S0%")
|rename ID_EMP as code
| join type=left [|  inputlookup append=t Emp.csv
| table code, name, STATUS
]| fillnull value="Blank" STATUS
| dedup code
| where STATUS!="Blank"

[nadlurinadluri]

Please find the sample query with the data points, hope it helps you to change your query accordingly - 

Also, in most of the cases you can skip using join command if one of your datasources is a lookupfile, you can use |lookup filename.csv command in those cases.

Considering the below is in lookupfile emp.,csv

code name
123 Smith
456 Myers

Final Query - 

| makeresults 
| eval BRAND_EMPID="Nike,123;Adidas,456;Lotto,123" 
| makemv delim=";" BRAND_EMPID 
| mvexpand BRAND_EMPID 
| eval Brand=mvindex(split(BRAND_EMPID,","),0) 
| eval ID_EMP=mvindex(split(BRAND_EMPID,","),1) 
| table Brand ID_EMP 
| lookup emp.csv Code AS ID_EMP
| table Brand ID_EMP NAME

 Let me know if the above query helped or if you feel anything needs to be changed in the above example.