Joining Tables with the Same Data But Different Column Names

Question

coachID and teamCoach contain the same data but have different column names. I'm having trouble joining them as a result. What can I do?

SELECT tblCoaches.coachID AS "Coach ID", 
       tblCoaches.coachName AS "Coach Name", 
       tblCoaches.coachCity AS "Coach City", 
       tblTeams.teamName AS "Team Name"
FROM tblCoaches
INNER JOIN tblTeams ON **tblCoaches.coachID = tblTeams.teamCoach**
      AND (coachID IN (SELECT teamCoach FROM tblTeams))
WHERE teamIsRec IS NULL;

Aleksejs R · Accepted Answer · 2018-04-19 22:01:17Z

1

If you want to make join on specific columns:

select * from table2 JOIN table1 ON table2.id = table1.id_a

In your case, I think that the line

AND (coachID IN (SELECT teamCoach FROM tblTeams))

doesn't perform any logic because INNER JOIN takes only those column values that are both in two tables. So you syntax should looks like:

SELECT tblCoaches.coachID AS "Coach ID", tblCoaches.coachName AS "Coach Name", tblCoaches.coachCity AS "Coach City", tblTeams.teamName AS "Team Name"
FROM tblCoaches
INNER JOIN tblTeams ON tblCoaches.coachID = tblTeams.teamCoach
WHERE teamIsRec IS NULL;

answered Apr 19, 2018 at 22:01

Aleksejs R

5172 gold badges5 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user9427074 Over a year ago

Okay, so as long as the data is the same, it doesn't matter that the names don't match. Thank you!

RoMEoMusTDiE Over a year ago

Good job for copying my answer

Aleksejs R Over a year ago

Or maybe you mine?

RoMEoMusTDiE · Accepted Answer · 2018-04-19 21:42:05Z

0

This structure should work

    SELECT
      tblCoaches.coachID AS "Coach ID",
      tblCoaches.coachName AS "Coach Name",
      tblCoaches.coachCity AS "Coach City",
      tblTeams.teamName AS "Team Name"
    FROM tblCoaches
    INNER JOIN tblTeams
      ON tblCoaches.coachID = tblTeams.teamCoach   -- IDs that linked the tables
    WHERE teamIsRec IS NULL;

answered Apr 19, 2018 at 21:42

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

2 Comments

user9427074 Over a year ago

So you removed the AND statement (which I kind of just threw in there, to be honest), but I've already tried that. It doesn't give me any results. Regardless, it's acceptable to say ON tblCoaches.coachID = tblTeams.teamCoach? Even though the column names aren't the same?

RoMEoMusTDiE Over a year ago

You did not post your table definition, how can I tell if you did the right join.... i.e. if TeamCoach is an ID then should be able to join it with the CoachID otherwise replace teamcoach with something else that identifies the coach

Collectives™ on Stack Overflow

Joining Tables with the Same Data But Different Column Names

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related