规范化sql join [英] normalization sql join

问题描述

祝你好运，



我有一张名为 organization 的表，其中有两个culumns id OrgName

现在，我在另一张表中使用组织表名为 JobTable 的列为： id ， jobId ， pickOrgname ， DropOrgName 。

在 pickOrgname 和D中 ropOrgName 我正在存储组织表的 id 值，以便稍后我将使用它在网页中显示，以使用 id 获取选择/ orgname



所以现在如何通过sql中的连接查询



i试过，但是失败了

< pre lang =SQL> 选择 jobId，O.OrgName as PickOrg，O .OrgName as DropOrg 来自组织O join JobTable J
J.pickOrgname = O.id 和 J.DropOrgName

< br $> b $ b

请帮我查询

 jobid PickOrg DropOrg 
 1 X Org Y org 
 2 A Org B Org 

我可以通过子查询来实现，但与联接有关

解决方案

如果我理解你是否想使用不同ID的同一个表jobtable，你可以再次添加工作表并加入不同的栏目试试这个

 选择 jobId，J.OrgName 为 PickOrg，J1.OrgName  as  DropOrg 来自组织O  join  JobTable J  on  
 J.IDJobTable = O.id  join  
 JobTable J1  on  J1.IDJobTable = O.id 

问候

Hanssel Navarro

您需要从 JobTable 驱动您的查询，然后对组织表进行INNER JOIN以获取（组织的）名称

但是你需要连接两次，因为你的工作表中有2个外键。例如

 选择 jobId，OPICK.OrgName，Odrop.OrgName 
 来自 jobtable J 
  inner   join 组织Odrop 上的<= span> > j.DropOrgName = odrop.id 
  inner   join 组织Opick   j.pickOrgname = opick.id 

注意 - 第一次加入组织是获取 drop 组织的名称，第二个是获取选择组织的名称。您真正感兴趣的是作业 ...所以首先将其放入您的查询中。



是 - 人我们会争辩说，如果你使用内连接，那么表格出现的顺序并不重要......但是当你在几天，几周，几个月，几年内回到你的代码时，它会让你更清楚你想要的是什么做（来自旧滞后的一点建议）

hi good day,

I have table named "organization" with two culumns "id" "OrgName"
Now, i am using that "organization" table in another table named "JobTable" with columns: id, jobId, pickOrgname, DropOrgName.
In the pickOrgname and DropOrgName i'm storing the "id" value of "Orgnization" table so later i'll use it to display in web page to get the pick/orgname by using the id

so now how to query by joins in sql

i tried, but failed

select jobId,  O.OrgName as PickOrg,O .OrgName as DropOrg from organization O join JobTable J on
J.pickOrgname =O.id and J.DropOrgName

pls help me to get query like

jobid PickOrg DropOrg 
 1      X Org  Y org 
 2      A Org  B Org

I can do it by sub query but is there to do with joins

解决方案

If I understand do you want to use the same table "jobtable" with diferent IDs maybe you can add the jobtable again and join with different columns try this

select jobId,  J.OrgName as PickOrg, J1.OrgName as DropOrg from organization O join JobTable J on
J.IDJobTable =O.id  join
JobTable J1 on J1.IDJobTable = O.id

Greetings
Hanssel Navarro

You need to drive your query from the JobTable, then do an INNER JOIN to the organisation table just to get the name (of the organisation)
But you need to do that join twice because you have 2 foreign keys in your jobtable table. E.g.

select jobId, OPICK.OrgName, Odrop.OrgName
from jobtable J
inner join organization Odrop on j.DropOrgName = odrop.id
inner join organization Opick on j.pickOrgname = opick.id

Note - the first join to organization is to get the name for the drop organisation, the second is to get the name for the pick organisation. The thing that you are really "interested in" is the Job ... so put that into your query first.

Yes - people will argue that it doesn't matter what order the tables appear in if you are using inner joins ... but when you come back to your code in days, weeks, months, years to come it makes it clearer what you were trying to do (bit of advice from an old lag)

这篇关于规范化sql join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！