Using an Inner Join and returning only 1 record per match on the left

I don’t know why I’m not able to figure this out ATM. Here’s a very contrived example.

create table Dog (
  [Name] varchar(10), 
  [BreedId] int
);

create table Breed (
  [BreedId] int,
  [Name] varchar(10)
);

insert into DOG (name, breedid)
VALUES ('Mix', 1),
  ('Mix2', 2),
  ('Mix2', 3);

insert into breed(breedid, name)
VALUES 
  (1, 'BullDog'),
  (1, 'Poodle'),
  (2, 'BullDog'),
  (2, 'Poodle'),
  (4, 'Poodle');

The following produces two rows because the join table has two matching values, I just want one value as long as there is a matching record on the right.

select d.*
from dog d
inner join breed b on d.breedid = b.breedid 

results in:

Mix 1

Mix 1

Mix 2

Mix 2

Is there a way to do this in the join clause / without doing a select in the where clase or other options like group by.

select d.*
from dog d
where d.breedid in (select breedid from breed)

Answer

Try “select distinct”. You can also group by the columns that are duplicates and take the max() of columns that have unique values.

Edit: how to filter by breed.breedid without using a where clause.

select d.*
From dog as d
Inner join (select distinct breedid from breed) as b on b.breedid = d.breedid