CODEWITHSUNDEEP
mysql
Scot Nery
Scot Nery

Reputation: 689

query getting too many results left join

I'm getting tons of rows, but my initial (LEFT) table "challenges_day3" only has one row.

SELECT *,challenges_day3.feedback AS feedback_d3,challenges_day3.feedback_please AS feedback_please_d3
FROM challenges_day3 
LEFT JOIN challengers ON challenges_day3.code_name=challengers.code_name 
LEFT JOIN challenges_day1 ON challengers.code_name=challenges_day1.code_name 
LEFT JOIN challenges_day2 ON challenges_day1.code_name=challenges_day2.code_name

I was expecting to get only one row result. Am I using left join wrong to achieve this?

Upvotes: 1

Views: 315

Answers (1)

Schwern
Schwern

Reputation: 164829

If there are multiple matches in the join tables, each will return their own row. For example, if c3.code_name is 'James Bond' and there are two rows in challengers with the code name 'James Bond' you will get two rows.

If you expect code_name to be unique per table you should mark it as such in the table schema. Better yet, challengers should be referenced not by their name (which can change) but by their ID as a foreign key.

create table challengers (
  id bigint primary key auto_increment,

  -- No two challengers can have the same code names.
  code_name varchar(255) not null unique
);

create table challenges_day3 (
  -- Only one row per challenger per day.
  challenger_id bigint not null unique,

  -- Declared as a foreign key so we know it exists in challengers.
  constraint fk_challenger
    foreign key(challenger_id) references challengers(id)

  -- and the rest of the columns.
);

Then you use the ID, which does not change and is unique for each table, to join.

SELECT
  *,
  challenges_day3.feedback AS feedback_d3,
  challenges_day3.feedback_please AS feedback_please_d3
FROM challenges_day3 c3
LEFT JOIN challengers c ON c3.challenger_id = c.id
LEFT JOIN challenges_day1 c1 ON c1.challenger_id = c3.challenger_id
LEFT JOIN challenges_day2 c2 ON c2.challenger_id = c3.challenger_id

Upvotes: 1

Related Questions