CODEWITHSUNDEEP
sql
phan
phan

Reputation: 27

Distinct with join that has same column name

this is a question online and i cant seem to get the expected output.

TABLE : dogs

  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL

TABLE : cats

  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL

it is to find the distinct name of this 2 table.

expected output is this

-- name     
-- -----
-- Bella    
-- Kitty    
-- Lola

i tried join and distinct resulting in saying my name is ambiguous. tired union but cant get to distinct it.

Upvotes: 1

Views: 336

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

To get the distinct list of names you can use union

select name from dogs
union 
select name from cats

or if your database supports FULL OUTER JOIN then 

select distinct coalesce(d.name,c.name)
from dogs d
full outer join cats c on d.name = c.name

Upvotes: 1

Related Questions