blocks|key|564554|text|我认为，您当前的设计/方法是可以接受的，对两个联接的需求取决于有两个用户ID列。这两列中的每一列都需要单独的联接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|564555|为了好玩，这里有一个表格设计，如果您真的想只执行一个连接，可以考虑这个设计：|564556|%2B------------%2B-----------%2B-------------%2B
%7C+product_id+%7C+user_id+++%7C+type++++++++%7C
%2B------------%2B-----------%2B-------------%2B
%7C+++++++1++++%7C+1+++++++++%7C+created+++++%7C
%7C+++++++2++++%7C+1+++++++++%7C+created+++++%7C
%7C+++++++3++++%7C+2+++++++++%7C+created+++++%7C
%7C+++++++4++++%7C+2+++++++++%7C+created+++++%7C
%7C+++++++5++++%7C+2+++++++++%7C+created+++++%7C
%7C+++++++1++++%7C+3+++++++++%7C+modified++++%7C
%7C+++++++2++++%7C+3+++++++++%7C+modified++++%7C
%7C+++++++3++++%7C+3+++++++++%7C+modified++++%7C
%7C+++++++4++++%7C+1+++++++++%7C+modified++++%7C
%7C+++++++5++++%7C+3+++++++++%7C+modified++++%7C
%2B------------%2B-----------%2B-------------%2B|code-block|syntax|javascript|564557|现在，您可以只使用一个单独的连接，然后是一个聚合：|564558|SELECT
++++p.product_id,
++++MAX(CASE+WHEN+t.type+=+'created'++THEN+u.Name+END)+AS+CreateByName,
++++MAX(CASE+WHEN+t.type+=+'modified'+THEN+u.Name+END)+AS+ModifiedByName
FROM+Product+p
INNER+JOIN+user+u
++++ON+p.user_id+=+u.Id
GROUP+BY
++++p.product_id;|564559|请注意，我根本不推荐这种方法。使用当前的方法和使用两个联接要干净得多。使用一个或多个索引可以相当容易地优化联接。上述聚合方法的性能可能不如您已有的方法。|564560|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Your current design/approach is acceptable, I think, and the need for two joins is a function of there being two user ID columns. Each of the two columns requires a separate join.

For fun, here is a table design which you may consider if you really want to have to perform only one join:

<pre><code>+------------+-----------+-------------+
| product_id | user_id | type |
+------------+-----------+-------------+
| 1 | 1 | created |
| 2 | 1 | created |
| 3 | 2 | created |
| 4 | 2 | created |
| 5 | 2 | created |
| 1 | 3 | modified |
| 2 | 3 | modified |
| 3 | 3 | modified |
| 4 | 1 | modified |
| 5 | 3 | modified |
+------------+-----------+-------------+
</code></pre>

Now, you can get away with a just a single join followed by an aggregation:

<pre><code>SELECT
 p.product_id,
 MAX(CASE WHEN t.type = 'created' THEN u.Name END) AS CreateByName,
 MAX(CASE WHEN t.type = 'modified' THEN u.Name END) AS ModifiedByName
FROM Product p
INNER JOIN user u
 ON p.user_id = u.Id
GROUP BY
 p.product_id;
</code></pre>

Note that I don't recommend this approach at all. It is much cleaner to use your current approach and use two joins. Joins can fairly easily be optimized using one or more indices. The above aggregation approach would probably not perform as well as what you already have.

blocks|key|1197194|text|如果您使用的是自然密钥而不是代理，则根本不需要加入。我不知道你如何在现实世界中区分你的产品，但举个例子，我假设你有一个UPC|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1197195|CREATE+TABLE+User+
(Name+VARCHAR(20)+PRIMARY+KEY);
CREATE+TABLE+Product+
(UPC+CHAR(12)+PRIMARY+KEY,+
CreatedBy+VARCHAR(20)+REFERENCES+User(Name),
ModifiedBy+VARCHAR(20)+REFERENCES+User(Name)
);|code-block|syntax|javascript|1197196|现在，您的查询是一个简单的选择，您还可以强制您的用户名的唯一性作为奖励，并且不需要额外的索引。试试吧..。|1197197|HTH|1197198|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you use natural keys instead of surrogates, you won't need to join at all.
I don't know how you tell your products apart in the real world, but for the example I will assume you have a UPC

<pre><code>CREATE TABLE User 
(Name VARCHAR(20) PRIMARY KEY);
CREATE TABLE Product 
(UPC CHAR(12) PRIMARY KEY, 
CreatedBy VARCHAR(20) REFERENCES User(Name),
ModifiedBy VARCHAR(20) REFERENCES User(Name)
);
</code></pre>

Now your query is a simple select, and you also enforce uniqueness of your user names as a bonus, and don't need additional indexes.
Try it...

HTH

blocks|key|1197209|text|联接是最好的方法，但如果寻找替代方法，则可以使用内联查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1197210|SELECT+P.PRODUCT_ID,
+++++++(SELECT+[NAME]+FROM+@USER+WHERE+ID+=+CREATED_BY)+AS+CREATED_BY,
+++++++(SELECT+[NAME]+FROM+@USER+WHERE+ID+=+MODIFIED_BY)+AS+MODIFIED_BY
FROM+@PRODUCT+P|code-block|syntax|javascript|1197211|演示|offset|length|1197212|entityMap|0|LINK|mutability|MUTABLE|url|https://rextester.com/NGA38136^0|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Join is the best Approach, but if looking for alternate approach you can use Inline Query.

<pre><code>SELECT P.PRODUCT_ID,
 (SELECT [NAME] FROM @USER WHERE ID = CREATED_BY) AS CREATED_BY,
 (SELECT [NAME] FROM @USER WHERE ID = MODIFIED_BY) AS MODIFIED_BY
FROM @PRODUCT P
</code></pre>

<a href="https://rextester.com/NGA38136" rel="nofollow noreferrer">DEMO</a>

How do I link 1 table with multiple columns in another table without using mutiple JOIN query?
Below is my scenario:

I have table <code>User</code> with <code>ID</code> and <code>Name</code>

<pre><code>User
+---------+------------+
| Id | Name |
+---------+------------+
| 1 | John |
| 2 | Mike |
| 3 | Charles |
+---------+------------+
</code></pre>

And table <code>Product</code> with multiple columns, but just focus on 2 columns <code>CreateBy</code> And <code>ModifiedBy</code>

<pre><code>+------------+-----------+-------------+
| product_id | CreateBy | ModifiedBy |
+------------+-----------+-------------+
| 1 | 1 | 3 |
| 2 | 1 | 3 |
| 3 | 2 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 3 |
+------------+-----------+-------------+
</code></pre>

With normal <code>JOIN</code>, i will need to do 2 JOIN:

<pre><code>SELECT p.Product_id,
 u1.Name AS CreateByName,
 u2.Name AS ModifiedByName
FROM Product p
JOIN USER user u1 ON p.CreateBy = u1.Id,
JOIN USER user u2 ON p.ModifiedBy = u2.Id
</code></pre>

to come out result

<pre><code>+------------+---------------+-----------------+
| product_id | CreateByName | ModifiedByName |
+------------+---------------+-----------------+
| 1 | John | Charles |
| 2 | John | Charles |
| 3 | Mike | Charles |
| 4 | Mike | John |
| 5 | Mike | Charles |
+------------+---------------+-----------------+
</code></pre>

How do i avoid that 2 times <code>JOIN</code>?

I'm using MS-SQL , but open to all SQL query for my own learning curious

Join Lookup from 1 table to multiple columns

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何将1表与另一表中的多列链接，而不使用多个联接查询？以下是我的设想：我有带ID和Name的桌子User+---------+------------+|      Id |       Name |+---------+------------+|       1 |       John ||       2 | ...

问从1个表到多个列的联合查找
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从1个表到多个列的联合查找EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从1个表到多个列的联合查找
EN