I was recently asked the following SQL question
There are 3 tables.
cat dog animal
id, name id, name id, color
what's the sql for listing the name of the animals with color = brown?
Can u help me?
One way would be"
select name from (cat Union dog) join (animal) on id where color='brown';
select a.name from cat a, animal b where b.color='brown' and a.id=b.id;
this will definitely do.................
select a.name from cat a, animal b where b.color='brown' and a.id=b.id;
this will definitely do.................
well,
Leader might be wrong , since it will only list out name of cats.
Another way (just to simplify m$hacks query)
select name from (
-- will give you all the cat names with color=brown
(select name from cat c, animal a where a.color='brown' and a.id=c.id)
union
-- will give you all the dog names with color=brown
( select name from dog d, animal a where a.color='brown' and a.id=d.id)
)
thanx,
I think techGuy's soln will work. But is there a way to make it cleaner, concise?
m$hacks soln was definitely a starter, but I don't know whether you can do
select field from (table1 union table2)
leader's won't work precisely because it only returns for 1 type - 'cat' where as the Q is asking for both cats and dogs.
experts, what assumption are you making about the foreign keys ? How can a single field id on animal table have foreign key to id's of two different tables ?
why is it not possible? cat
animal |------------------- id
id --------------------------| name
color | dog
|------------------ id
name
Select name from Cat
UNION ALL //(or just UNION)
Select name from Dog
where id = (Select id from Animal where color = "brown")
here is answer,
select * from cat a
inner join dog b
on a.id =b.id
inner join animal c
on b.id =c.id
where c.color = 'Brown'
select a.name, b.name from cat a
inner join dog b
on a.id =b.id
inner join animal c
on b.id =c.id
where c.color = 'Brown'
Both from table A and B.
or USE * which will give you all.
yak yak yak,
we just need a single column with the names of cats and dogs with color brown.
Your 2<sup>nd</sup> sql if it works will produce 2 columns with names of cats and separate column with names of dogs.
select a.name from cat a, animal b where a.id = b.id and b.color='Brown'
union all
select a.name from dog a, animal b where a.id = b.id and b.color='Brown'
btw, I don't like the schema, why the need for dog and cat table seperately?
that's for normalization.
i ran this and works
assuming that id in cats and/or dogs are foreign keys from animals. let me know other wise
select name from cats where id in (select id from animals where color = 'brown')
union
select name from dogs where id in (select id from animals where color = 'brown')
Please log in to reply to this post
You can also log in using your Facebook
What people are reading
You might like these other discussions...
· Posts 3
· Viewed 704
· Likes 1
· Posts 72
· Viewed 16138
· Likes 15
· Posts 16
· Viewed 2528
· Likes 3