I presently have 2 tables:

Table Animal:
animal_id, animal_name owner

Table Owners
owner_id owner_name

One method to list all of the creatures as well as their particular proprietors it to make use of sql joins:

select animal_id, owner_name
from Animals, Owners
where (owner = owner_id);

I'd now prefer to understand how to perform the same goes with subqueries. I figured something similar to

select animal_id, owner_name
from Animals
where owner_name = (select owner_name from Owners where owner = owner_id)

but that does not appear to create the secret. Why?

If you would like the dog owner_title area incorporated within the results, which area is incorporated in the table Proprietors, then you definitely must JOIN Proprietors in to the query in some way. The only real factor you are able to return included in the result set are posts in the incorporated tables, constants, or derived values (using Oracle language functions to function on posts and/or constant values).


Really, since I consider it, you could utilize (or, rather, try — I am no Oracle user) this very-poorly-carrying out syntax:

 SELECT animal_id, (SELECT owner_name FROM Owners WHERE owner = Animals.owner_id)
   FROM Animals

That's, technically, a sub-query solution.

It does not work because you've got a column 'owner_name' that does not appear in a table inside your from clause. To incorporate a sub query increase the risk for choose, range from the sub query inside your choose clause:

select animal_id, (select owner_name from Owners where owner = owner_id)
from Animals

That which you me is as an implicit Cartesian join.

select animal_id, owner_name
from Animals, Owners
where (owner = owner_id);

An alternate is inner joins

select animal_id, owner_name
from Animals A inner join Owners O
     on A.owner = O.owner_id;

It appears as if you can't stand a subquery within the Choose clause. I do not either. There similar things that you can do wityhout while using actual word 'JOIN' but normally, this is considered best.

I believe you might have a design flaw: owner seems to not be a characteristic of the animal.

Rather, possession might be rapport between creatures the ones (or whatever entity the owner is). Therefore, you should look at getting three tables e.g. Animals (entity table), People (or whatever entity table) and Ownership (rapport table).