I Have A Stored Procedure That Needs To Pull Info From The Same Table Twice. How Do I Use Correlation Names?

I am creating a game cheat page for the game Doodle Creatures. The table I have created takes the ID's of the Combo Animal, the Gene it is combined with, and that of the New animal

Solution 1:

If that's the case, all you need to do is to supply an ALIAS on the table name to avoid name collision. eg

SELECT * 
FROM [CreatureCombos]
     INNER JOIN [Animals] AS Animal1 
           ON [CreatureCombos].[NewCreatureID] = Animal1.[AnimalId]
     INNER JOIN [Animals] AS Animal2 
           ON [CreatureCombos].[ComboAnimalID] = Animal2 .[AnimalId]
     INNER JOIN [Genes] 
           ON [CreatureCombos].[ComboGeneID] = [Genes].[GeneId] 

Solution 2:

I think there is problem in table design.In [CreatureCombos] remove both column [NewCreatureID] and [ComboAnimalID].Add one column call AnimalID and second Column as AnimalType int( 1,2, etc)

So in this design there will only one Animal1 join and with type you can identify AnimalType.

Also if t'row there is more animaltype or category then what you will do ?

So recommended design is more flexible and open type .