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.................

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

jeffali,

We can definitely have foreign key reference as in the following web page (http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-serveryou) you can see that Students, Teachers, and Parents are all "People" and Students, Teachers id have reference to the people's id.

techGuy's diagram looks skewed. U mean to say that u'r sql works as is?

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^nd 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')