加入不同表的多个字段 [英] Join Multiple Fields Of Different Tables

问题描述

hi


请在下列情况下帮助我。我不明白用什么类型的查询来获得结果。



我有4个表，我希望user_name基于IncidentID。但是Username属于不同的表，并且与它们相关的字段也存在于不同的表中。但所有表都属于同一个数据库。



table_incident.Id

table_incident.Entitydimkey



table_workitemdim.Entitydimkey

table_workitemdim.workitemdimkey



table_workitemassign.workitemdimkey

table_workitemassign.workitemassign_userdimkey



table_userdim.userdimkey

table_userdim.firstname



请告诉我紧急应该怎么做我... ..



提前致谢

问候

M Ahsan Misbah

hi
Please help me out in the following situation. i dont understand what type of query used to get result.

I have 4 tables and i want user_name on the basis on IncidentID. But Username is belongs to a different table and the fields which relate them also present in different tables. But All tables belongs to same DB.

table_incident.Id
table_incident.Entitydimkey

table_workitemdim.Entitydimkey
table_workitemdim.workitemdimkey

table_workitemassign.workitemdimkey
table_workitemassign.workitemassign_userdimkey

table_userdim.userdimkey
table_userdim.firstname

Please tell me urgent what should i do..

Thanks in advance
Regards
M Ahsan Misbah

推荐答案

从table_userdim内部联接中选择名字

table_incident on（table_userdim.userdimkey = table_incident.entitydimkey）内部联接

table_workitemdim on（table_incident。 entitydimkey = table_workitemdim.Entitydimkey）内部联接

table_workitemassign

（table_workitemdim.Entitydimkey = table_workitemassign.workitemassign_userdi mkey）

其中

table_incident.id = @id





或者你也可以通过这种方式实现这个改变....





从table_userdim内部联接中选择名字

table_workitemassign

on（table_userdim.userdimkey = table_workitemassign.workitemassign_userdimkey）内部联接

table_workitemdim on（ttable_workitemassign.workitemdimkey = table_workitemdim .entitydimkey）内部联接

table_incident on（table_incident.entitydimkey = table_workitemdim.Entitydimkey）



where

table_incident.id = @id

Select firstname from table_userdim inner join
table_incident on (table_userdim.userdimkey = table_incident.entitydimkey)inner join
table_workitemdim on (table_incident.entitydimkey = table_workitemdim.Entitydimkey) inner join
table_workitemassign on
(table_workitemdim.Entitydimkey = table_workitemassign.workitemassign_userdimkey)
where
table_incident.id = @id


Or you can also achieve this to change in this way ....


Select firstname from table_userdim inner join
table_workitemassign
on (table_userdim.userdimkey = table_workitemassign.workitemassign_userdimkey)inner join
table_workitemdim on (ttable_workitemassign.workitemdimkey = table_workitemdim .entitydimkey)inner join
table_incident on (table_incident.entitydimkey = table_workitemdim.Entitydimkey)

where
table_incident.id = @id

尝试以下查询

Hi,
try below query

--this query will give first name of the record with id = 10
SELECT t4.firstname 
FROM table_incident AS t1 
INNER JOIN table_workitemdim AS t2 ON t2.Entitydimkey = t1.Entitydimkey 
INNER JOIN table_workitemassign as t3 ON t3.workitemdimkey = t2.workitemdimkey
INNER JOIN table_userdim as t4 ON t4.userdimkey = t3.workitemassign_userdimkey
WHERE t1.ID = 10

希望有帮助

hope it helps

你的架构需要引起注意......目前还不清楚Entitydimkey应该指向什么表。



你需要做些什么研究o n表中的外键。

一旦你掌握了这个概念，就需要对SQL JOINS做进一步的研究。



作为一个例子，我将在事件表的用户表上有一个外键（FK），这样我就可以轻松确定哪个用户引发了事件。然后我会沿着这些行查询...

Your schema needs some attention ... it''s not clear what table is Entitydimkey supposed to "point" to.

You will need to do some research on Foreign Keys in tables.
Once you''ve grasped that concept you will need to do some further research on SQL JOINS.

As an example I would have a foreign key (FK) to the user table on the incident table so that I could easily determine which user raised the incident. I would then query along these lines...

SELECT * FROM table_incident I
INNER JOIN table_userdim U on I.userdimkey=U.userdimkey

顺便说一句，它不是一个好的表格来为你的表添加前缀单词table

As an aside, it''s not considered good form to prefix your tables with the word "table"

这篇关于加入不同表的多个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！