表连接引用相同的主键T-SQL

Is it valid to have a Table which references the same Primary Key to assign a value to multiple of it's Foreign Key? I.E. :

有一个表引用相同的主键来为其多个外键分配值是否有效? I.E. :

Table Menu

ItemID(PK)  DishName        Category
  1         Fried Chicken   Poultry
  2         Salmon          Fish
  3         Spaghetti       Pasta
  4         Steak           Beef
  5         Roasted Cod     Fish
  6         Baked Halibut   Fish
  7         Maple Duck      Poultry

Table [TableOrder] where OrderItem# refers to the primary key of the Menu table.

Table [TableOrder]其中OrderItem#指的是Menu表的主键。

PartyID OrderItem1(FK)  OrderItem2(FK)  OrderItem3(FK)
    1       3               2               5
    2       7               1               2
    3       2               5               6
    4       4               4               2
    5       6               3               2

Is it possible (how could you) to return a query that lists the category as such? (Where Order#Cat is an alias to Category in the Menu table using an (Inner) Join)

是否有可能(如何)返回列出类别的查询? (其中Order#Cat是使用(内部)连接的Menu表中Category的别名)

PartyID Order1Cat  Order2Cat  Order3Cat
  1     Pasta       Fish        Fish    
  2     Poultry     Poultry     Fish    
  3     Fish        Fish        Fish    
  4     Beef        Beef        Fish    
  5     Fish        Pasta       Fish

As in

 SELECT        
     [Table Order].PartyID, [Table Order].OrderItem1, 
     [TableOrder].OrderItem2, [Table Order].OrderItem3, 
     Menu.Category
 FROM
     [Table Order] 
 INNER JOIN
     Menu ON [Table Order].OrderItem1 = Menu.ItemID
     ...
     (alias ?)...
     (subquery ?)...

If not, please suggest an alternative structure that would permit similiar results. Thanks for your time.

如果没有,请建议一个允许类似结果的替代结构。谢谢你的时间。

1 个解决方案

#1

try :

SELECT tblo.PartyID, 
       tm1.Category Order1Cat,
       tm2.Category Order2Cat,
       tm3.Category Order3Cat
FROM [Table Order] tblo
INNER JOIN [Table Menu] tm1
    ON tblo.OrderItem1 = tm1.ItemID
INNER JOIN [Table Menu] tm2
    ON tblo.OrderItem2 = tm2.ItemID
INNER JOIN [Table Menu] tm3
    ON tblo.OrderItem3 = tm3.ItemID