搜索
搜索

two foreign keys to same primary key select statement MYSQL

Wild Man 发表于 Dev
13
Sajjad Rashid

first Table "teams" has TeamCode(varchar 5) and TeamName (varchar 20) second Table "season" has homeTeam (varchar 5) , Team2 (varchar 5), Gameday (date)

homeTeam & Team2 are FKs that are connected to TeamCode PK

table: teams

| TeamCode | TeamName |
|:-----------:|:--------------|
| 1 | USA |
| 2 | UK |
| 3 | JAPAN |

table: season

each team plays the other once as a home team

| Team1 | Team2 |Gameday
|:-----:|:------|:------|  
| 1     | 2     | 7  jan|
| 1     | 3     | 14 jan|
| 2     | 1     | 21 jan|  
| 2     | 3     | 28 jan|  
| 3     | 1     | 4  feb|
| 3     | 2     | 11 feb|

I want a query that would display the Team names and the day they will play together

So it should look like

HomeTeam Name |   Team2 Name  |  Gameday
Hadi Mohammadi

Try this

SELECT
     T1.Name As Host ,
     T2.Name As Guest,
     S.Date 
FROM [dbo].[Season] as S
Inner Join [dbo].[Team] as T1 on S.HostTeam = T1.ID
Inner Join [dbo].[Team] as T2 on S.GuestTeam = T2.ID

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

Relating two foreign key columns in Table A to a primary key column in Table B

来自分类Dev

SELECT with multiple PRIMARY KEY

来自分类Dev

MYSQL：基于SELECT结果的ALTER TABLE DROP FOREIGN KEY

来自分类Dev

即使Foreign_keys为ON，FOREIGN KEY约束也会失败

来自分类Dev

Relationship between tables with two foreign keys

来自分类Dev

MySQL / MariaDB在PRIMARY KEY上的慢速更新

来自分类Dev

MySQL / MariaDB在PRIMARY KEY上的慢速更新

来自分类Dev

MYSQL can't add a foreign key

来自分类Dev

无法在MySQL中创建FOREIGN KEY CONSTRAINT

来自分类Dev

MySQL how to cascade on delete without foreign key

来自分类Dev

How to define foreign key relationships in sails with mysql

来自分类Dev

mysql InnoDB：FOREIGN KEY约束性能

来自分类Dev

MySQL FOREIGN KEY错误，已删除级联

来自分类Dev

SQL query for selecting foreign-key rows with more than one link to the primary-key table

来自分类Dev

INSERT语句与FOREIGN KEY SAME TABLE约束冲突

来自分类Dev

Create ID automatically for all entries into tables with same foreign key

来自分类Dev

EF 7：INSERT语句与FOREIGN KEY SAME TABLE冲突

来自分类Dev

如何修复：UPDATE 语句与 FOREIGN KEY SAME TABLE 约束冲突

来自分类Dev

带有ServiceStack V3.9的简单SELECT FOREIGN KEY

来自分类Dev

Joining two tables with one table having a different foreign key

来自分类Dev

MYSQL PRIMARY CHAR KEY的最大最佳大小是多少

来自分类Dev

应该有class_name，foreign_key和primary_key的have_many

来自分类Dev

为什么在MYSQL中不能将UNSIGNED用作FOREIGN KEY？

来自分类Dev

SQLite with NULLABLE foreign keys

来自分类Dev

Using MYSQL are keys required on individual fields if in a unique key?

来自分类Dev

Django Custom primary key

来自分类Dev

将 CREATE TABLE、ADD COLUMNS、PRIMARY KEY、AS SELECT 和 WHERE 合并为一个操作

来自分类Dev

MySQL SELECT from two tables with COUNT

来自分类Dev

与FOREIGN KEY约束冲突

Related 相关文章

  1. 1

    Relating two foreign key columns in Table A to a primary key column in Table B

  2. 2

    SELECT with multiple PRIMARY KEY

  3. 3

    MYSQL：基于SELECT结果的ALTER TABLE DROP FOREIGN KEY

  4. 4

    即使Foreign_keys为ON，FOREIGN KEY约束也会失败

  5. 5

    Relationship between tables with two foreign keys

  6. 6

    MySQL / MariaDB在PRIMARY KEY上的慢速更新

  7. 7

    MySQL / MariaDB在PRIMARY KEY上的慢速更新

  8. 8

    MYSQL can't add a foreign key

  9. 9

    无法在MySQL中创建FOREIGN KEY CONSTRAINT

  10. 10

    MySQL how to cascade on delete without foreign key

  11. 11

    How to define foreign key relationships in sails with mysql

  12. 12

    mysql InnoDB：FOREIGN KEY约束性能

  13. 13

    MySQL FOREIGN KEY错误，已删除级联

  14. 14

    SQL query for selecting foreign-key rows with more than one link to the primary-key table

  15. 15

    INSERT语句与FOREIGN KEY SAME TABLE约束冲突

  16. 16

    Create ID automatically for all entries into tables with same foreign key

  17. 17

    EF 7：INSERT语句与FOREIGN KEY SAME TABLE冲突

  18. 18

    如何修复：UPDATE 语句与 FOREIGN KEY SAME TABLE 约束冲突

  19. 19

    带有ServiceStack V3.9的简单SELECT FOREIGN KEY

  20. 20

    Joining two tables with one table having a different foreign key

  21. 21

    MYSQL PRIMARY CHAR KEY的最大最佳大小是多少

  22. 22

    应该有class_name，foreign_key和primary_key的have_many

  23. 23

    为什么在MYSQL中不能将UNSIGNED用作FOREIGN KEY？

  24. 24

    SQLite with NULLABLE foreign keys

  25. 25

    Using MYSQL are keys required on individual fields if in a unique key?

  26. 26

    Django Custom primary key

  27. 27

    将 CREATE TABLE、ADD COLUMNS、PRIMARY KEY、AS SELECT 和 WHERE 合并为一个操作

  28. 28

    MySQL SELECT from two tables with COUNT

  29. 29

    与FOREIGN KEY约束冲突

热门标签

归档