SQL mapping between multiple tables

Question

This is a SQL design issue. Firstly, the setting. I have three tables:

• A, which is automatically populated based on a request on a linked server. The data in this table cannot be changed;
• B, which has just a dozen lines containing names for As collections;
• AtoB, which is the mapping table by which As are organized into named collections, with foreign keys in both columns;

For example, A contains:

• Giraffe
• Owl
• Tiger

And B contains:

• Seattle Zoo
• San Jose Zoo

And AtoB contains:

1.1 (Giraffe in Seattle)
 2.1 (Owl in Seattle)
 3.1 (Tiger in Seattle)
 2.2 (Owl in San Jose)

Now the problem is:

, A. , C , A, . , C :

• Dragon

, C AtoB? , ?

, , , V, A C, AtoB VtoB. , : .

, A OR C B.

Answer 1

Arthur Thomas union WHERE , :

SELECT A.Name as Animal, B.Name as Zoo FROM A, AtoB, B
    WHERE AtoB.A_ID = A.ID && B.ID = AtoB.B_ID 
UNION
SELECT C.Name as Animal, B.Name as Zoo FROM C, CtoB, B
    WHERE CtoB.C_ID = C.ID && B.ID = CtoB.B_ID

:

SELECT Animal FROM zoo_animals WHERE Zoo="Seattle Zoo"

Answer 2

Dragon A, . , ( ), , A. , (AtoB), , , A. , , :

imaginary_creatures

• ID

imaginary_creatures_to_b

• imaginary_creatures_id ( imaginary_creatures)
• b_id ( )

, , UNION

SELECT A.Name FROM A where A.ID IN 
   (SELECT AB.A_ID FROM AtoB AB WHERE B_ID = 
      (SELECT B.ID FROM B WHERE B.Name = 'Zoo Name'))
UNION
SELECT i.name FROM imaginary_creatures i i.id IN 
   (SELECT ic.imaginary_creatures_id FROM imaginary_creatures_to_c ic 
    WHERE ic.b_id = (SELECT B.ID FROM B WHERE B.Name = 'Zoo Name'))

, .

Answer 3

, - , , (A C) . , . .

Answer 4

, , Dragon A, A, , AtoB, LEFT OUTER JOIN. - :

SELECT * FROM A
LEFT OUTER JOIN AtoB
ON A.id = AtoB.A_ID

Edit: this will only work if you can add your new entries to A. I missed the fact that you cannot. I think the decision of Arthur Thomas is what you want.