Merge 3 tables (left join)

Question

0.00/5 (No votes)

See more:

I do have the following table

table Able

userid    date       source
 1010      4/20/2020   updown
 1012      3/20/2020   leftdown

Table Brake

userid    completed_team   crossing   purchase
  1010       tokyo            mapping    yes
  1012       New York         clive      yes

Table Cost

userid   _send  _open  _click  completed_team   
  1010     20     12      8          Only
  1012     12     8       6          Double

I'm trying to get a table that shows something like this

userid    date       source       send  open  click
1010      4/20/2020   updown       20     12      8
1012      3/20/2020   leftdown     12     8       6

What I have tried:

i've used this query, but it is returning duplicates even though there are no duplicates

SQL

SELECT Able.userid,Able.date,Able.ource,Brake.ompleted_team,cost.Send2,cost.Open2,cost.Click2
 from Brake
 left join Able on breake.userid= able.userid
 left join cost on able.userid= cost.userid

Posted 20-Apr-20 14:09pm

learning_new

Updated 1-May-20 19:43pm

Maciej Los

v3

Add a Solution

Comments

Tomas Takac 21-Apr-20 2:21am

With the data you show there should be no duplicates. Try to run this to see where it is duplicated:

select a.userid, b.userid, c.userid
from Able a
left join Brake b on b.userid = a.userid
left join Cost c on c.userid = a.userid

ZurdoDev 21-Apr-20 7:35am

Select all columns from all tables and then you can see where the duplicate is coming from.

1 solution

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)

Eyssan · Answer 1 · 2020-05-01T19:44:00

You have to use parentheses to separate each join. The query would be like this:

SQL

SELECT A.userid, A.date, A.source, B.completed_team, C.send, C.open, C.click
from (( B
left join A on B.userid = A.userid ) 
left join C on A.userid = C.userid);

The output of the above code will be:

userid	date	    source	 completed_team	send	open	click
1010	4/20/2020	updown	   Tokyo	     20	     12	     8
1012	3/20/2020	leftdown   New York	     12	     8	     6