Oracle: Select Subquery and Where

Question

I'm new to SQL and database design and trying to work out if the following is possible.

I have a table called cats, PKs (cat_id) are C1, C2, C3 etc and another attribute with cat names (plus multiple other attributes).

I have a table called dogs, PKs (dog_id) are D1, D2, D3 etc and another attribute with dog names (plus multiple other attributes).

I then have a table called animals, PK 1, 2, 3, 4, 5, 6, etc with attribute Cat_Dog_ID (C1, C2, C3, D1, D2, D3 etc).

What I then want to do is a select statement that will bring back the cat and dog names, eg.

An_Id  - Cat_Dog_ID - Name
1      - C1         - Henry
2      - C2         - Whiskers
3      - C3         - Grey
4      - D1         - Spotty
5      - D2         - Woof
6      - D3         - Max

To do it separately is easy enough:

SELECT a.an_id, a.cat_dog_id, c.cat_name 
FROM animal a, cat c
WHERE a.cat_dog_id = c.cat_id;

SELECT a.an_id, a.cat_dog_id, d.dog_name 
FROM animal a, dog d
WHERE a.cat_dog_id = d.dog_id;

I've tried numerous things to combine these together and pretty sure I need to use a subquery / nested query, eg as follows but havent been able to get anything to work so far.

SELECT a.an_id, a.cat_dog_id, c.cat_name "Name"
FROM animal a, cat c
WHERE a.cat_dog_id = c.cat_id
AND (SELECT a.an_id, a.cat_dog_id, d.dog_name "Name"
FROM animal a, dog d
WHERE a.cat_dog_id = d.dog_id);

SELECT a.an_id, a.cat_dog_id FROM animal a
where a.cat_dog_id = c.cat_id in (select c.cat_name "Name" from cat c)
WHERE a.cat_dog_id = d.dog_id in (select d.dog_name "Name" from dog d);

Any suggestions how to do this?

Answer 1

Making no comments about your design; but to aid in a solution to this specific problem: UNION is what you're after

SELECT a.an_id, a.cat_dog_id, c.cat_name 
FROM animal a, cat c
WHERE a.cat_dog_id = c.cat_id
UNION
SELECT a.an_id, a.cat_dog_id, d.dog_name 
FROM animal a, dog d
WHERE a.cat_dog_id = d.dog_id;

Now the reason I question the design is you can abstract cats/dogs into a table called animals and have a typeID field for "Cats" and one for "dogs" since dogs & cats share similar attributes you don't have different tables. This is called data normalization. In the purest sense, you never want to duplicate the same data. However, from performance standpoints it is sometimes worthwhile to duplicate. Without knowing the context of your problem I don't know if it would be wise to normalize in this case; but it likely is.

----To address the design issue consider the following:

Animal
--------------------
AnimalID
TypeID
Name
Birthdate
Gender
...etc

Type
--------
TypeId
TypeName - Cat or Dog or (now scales to other types of animals if needed w/o redsign of table)


Attributes
-----------
AttributeID
Name 
DataType (Numeric, String, date, etc)

TypeAttributes
-------------
AttributeID
TypeID

AnimalsAttributes
---------------
AttributeID
AnimalID
Value

Now you can scale for different types of animals, have a limitless number of attributes and retain values for the different animals added; all without having to change database structure. If you added a new attribute under your model, you'd have to change structure; and add new code that's no fun. However, if you don't anticipate any changes to the model; then your solution works fine.

Answer 2

Do one thing.

Use query1 UNION query2;

And give alias names(names in qoutes) to column names where different.

Example Select cat_name "name", cat_id "id" from cat_table union select dog_name "name", dog_id "id" from dog_table;

There are more complex ways...but this should do the trick for you...