我有一个需要拉从同一个表信息两次存储过程。我如何使用相关名？ [英] I have a stored procedure that needs to pull info from the same table twice. How do I use Correlation Names?

问题描述

我创建的游戏涂鸦生物游戏作弊网页。我已经创建了表取组合动物时，它与结合的基因的ID的，和从该动物表的新的动物，以显示信息。该表是这样的：

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 from the Animals table to display the information. The table looks like this:

Creature Combos Table:
ComboID (PK) | NewAnimalID (FK) | ComboAnimalID (FK) | GeneID (FK) |

我创建从连击表中选择的记录的过程填充我与有关组合动物，基因，并通过加入基于该组合，新的动物，以及基因的ID的动物和基因表新的动物的信息数据列表。

The procedure I created to select records from the Combos table populates my datalist with information about the combo animal, gene, and new animal by joining the Animal and Genes tables based on the ID's of the combo, new animal, and genes.:

CREATE PROCEDURE [dbo].[SelectCreatureCombos]
AS
    SELECT * FROM [CreatureCombos]
    INNER JOIN [Animals] ON [CreatureCombos].[NewCreatureID] = [Animals].[AnimalId]
    INNER JOIN [Animals] ON [CreatureCombos].[ComboAnimalID] = [Animals].[AnimalId]
    INNER JOIN [Genes] ON [CreatureCombos].[ComboGeneID] = [Genes].[GeneId] ;

程序期望的结果：

Desired Results of Procedure:

(New) Animal (Name) | AnimalImg | AnimalAltText | AnimalToolTip | (Combo) Animal (name) | AnimalImg | AnimalAltText | AnimalToolTip | Gene | GeneImg | GeneAltText | GeneToolTip | 

当我运行页面，我得到以下错误：

When I run the page, I get the following error:

The objects "Animals" and "Animals" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

我必须承认我还是有点新到SQL;我可以插入，选择和更新像亲，但连接和别名和相关名是有点超越我。任何其他建议将不胜AP preciated。谢谢！

I must admit I am still a little new to SQL; I can insert, select, and update like a pro, but joins and aliases and correlation names are a little beyond me. Any other advice would be greatly appreciated. Thanks!

推荐答案

如果是这样的话，所有你需要做的是在表名提供一个 ALIAS 避免名称冲突。例如：

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] 

这篇关于我有一个需要拉从同一个表信息两次存储过程。我如何使用相关名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！