SQL查询:复杂的内部联接和外部联接 [英] SQL Query: Complex Inner Joins and Outer Joins

问题描述

大家好，我目前正在研究一个SQL示例项目，试图学习SQL的基础知识和一般的内部外部联接.

这是我当前的数据库图:

http://imgur.com/z0Ger

我目前无法解决2个写困难的查询.

  1.给一个小狗的名字显示它知道的所有技巧.包括:狗的ID&名称技巧ID&名称学习日期和时间技能等级2.给狗窝看一下所有在那里学习技巧的小狗.包括:狗窝编号名称学习日期和时间技巧编号狗的ID&狗名 

我真的很困惑，如果您能帮助我找到正确的答案，我将不胜感激.任何帮助表示赞赏.谢谢

解决方案

首先，您在Junction_Table和Tricks之间缺少一个foriegn键，但是由于您拥有其他功能，因此我想您可以解决此问题.

第二，学习日期和技能水平属于联系表，而不是技巧表，因为这些是狗，狗窝和狗的组合的功能.绝招而不是绝招.

已解决该问题，第一个查询将赋予所有狗&技巧是:

  SELECT d.Dog_ID，d.Dog_Name，t.Trick_ID，t.Trick_Name，j.Skill_Level，j.Date_Learned从狗d内部联接Junction_Table j ON d.Dog_ID = j.Dog_ID内部联接技巧t ON t.Trick_ID = j.Trick_ID 

这些将为您提供至少学会了一项技巧的每一次操作所学到的所有技巧.

如果将Dogs和Junction_Table之间的INNER JOIN更改为LEFT JOIN，那么您将获得上述所有内容以及没有技巧的狗.

如果将其更改为RIGHT JOIN，您将获得1+技巧的狗以及包括没有狗在内的所有技巧.

如果将其更改为完全加入"，您将获得狗和猫"的惊艳.

如果将其更改为CROSS JOIN(并删除了ON语句)，您将得到一条针对每把戏的每条狗的一行.

第二个查询，我将让您找出答案.

关于连接没有什么棘手的问题-从所有表中所需的字段开始，然后逐步通过连接将它们绑定在一起.

Hi everyone I am currently working on an SQL sample project, trying to learn the basics of SQL and inner outer joins in general.

This is my current Database diagram:

http://imgur.com/z0Ger

I am currently stuck on 2 different queries that I am having trouble writing.

1. Given a puppy name show all the tricks it knows. 

Include:
Dog id & name
Trick id & name
Date learned & skill level

2. Given a kennel show all the puppies that have been there to learn a trick.

Include:
Kennel id & name
Date learned & trick id
Dog id & dog name

I really am stumped on these and would really appreciate it if you can help lead me to the right answer. Any help is appreciated. Thanks

解决方案

First, you have a missing foriegn key between Junction_Table and Tricks but since you got the others in I assume you can fix this.

Second, the date learned and skill level belong in the junction table, not the trick table as these are functions of the combination of the dog, kennel & trick and not of the trick alone.

Having fixed that, the first query to give all dogs & tricks is:

SELECT  d.Dog_ID
       ,d.Dog_Name
       ,t.Trick_ID
       ,t.Trick_Name
       ,j.Skill_Level
       ,j.Date_Learned
FROM   Dogs d
       INNER JOIN
       Junction_Table j ON d.Dog_ID=j.Dog_ID
       INNER JOIN
       Tricks t ON t.Trick_ID=j.Trick_ID

These will give you all the tricks learned by every do that has learned at least one trick.

If you changed the INNER JOIN between Dogs and Junction_Table to LEFT JOIN you would then get all the above plus the dogs that have no tricks.

If you changed it to RIGHT JOIN you would get the dogs with 1+ trick and all the tricks including those that have no dogs.

If you changed it to FULL JOIN you would get eveything from Dogs and Tricks.

If you changed it to CROSS JOIN (and removed the ON statement) you would get a line for every dog against every trick.

The second query one I will leave you to figure out.

There is nothing tricky about joins - start with the fields you need from all tables and then work your way through the connections to tie them together.

这篇关于SQL查询:复杂的内部联接和外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！