英文:
How do I join identical columns while also keeping separate unique columns from two tables?
问题
我尝试了以下查询，但是得到的结果是：
| Col 1 | Col 2 | Col 3 | Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
| ----- | ----- | ----- | ----- | ----- | ----- | ----- | ----- |
| Val A | Val C | Val E | Val A | Val C | Val E | Val X | Val Z |
| Val B | Val D | Val F | Val B | Val D | Val F | Val Y | Val K |
你想要的结果是：
| Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
| ----- | ----- | ----- | ----- | ----- |
| Val A | Val C | Val E | Val X | Val Z |
| Val B | Val D | Val F | Val Y | Val K |
为了获得你想要的结果，你可以尝试以下查询：
select
p.[Col 1],
p.[Col 2],
p.[Col 3],
e.[Col 4],
p.[Col 5]
from p
left join e on p.[Col 1] = e.[Col 1] and p.[Col 2] = e.[Col 2] and p.[Col 3] = e.[Col 3]
这应该会给你期望的结果。
英文:
I have two tables, E and P.
E:
| Col 1 | Col 2 | Col 3 | Col 4 |
|---|---|---|---|
| Val A | Val C | Val E | Val X |
| Val B | Val D | Val F | Val Y |
P:
| Col 1 | Col 2 | Col 3 | Col 5 |
|---|---|---|---|
| Val A | Val C | Val E | Val Z |
| Val B | Val D | Val F | Val K |
where Columns 1, 2, and 3 in both tables are identical in terms of both the name of the column and the values but column 4 in table E and column 5 in table P both have unique values.
How do I join these two tables so that I get:
EP:
| Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
|---|---|---|---|---|
| Val A | Val C | Val E | Val X | Val Z |
| Val B | Val D | Val F | Val Y | Val K |
I have tried:
select
p.[Col 1],
p.[Col 2],
p.[Col 3],
e.[Col 1],
e.[Col 2],
e.[Col 3],
p.[Col 5],
e.[Col 4]
from p
left join e on p.[Col 3] = E.[Col 3] and p.[Col 2] = e.[Col 2] and p.[Col 1] = e.[Col 1]
group by p.[Col 1], p.[Col 2], p.[Col 3], e.[Col 1], e.[Col 2], e.[Col 3]
But what I get is:
| Col 1 | Col 2 | Col 3 | Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
|---|---|---|---|---|---|---|---|
| Val A | Val C | Val E | Val A | Val C | Val E | Val X | Val Z |
| Val B | Val D | Val F | Val B | Val D | Val F | Val Y | Val K |
答案1
得分: 0
I think you're over complicating it substantially here.
SELECT
p.[Col 1],
p.[Col 2],
p.[Col 3],
e.[Col 4] AS [Col 4],
p.[Col 4] AS [Col 5],
FROM p INNER JOIN
e ON (p.[Col 3] = E.[Col 3] and p.[Col 2] = e.[Col 2] and p.[Col 1] = e.[Col 1])
英文:
I think you're over complicating it substantially here.
SELECT
p.[Col 1],
p.[Col 2],
p.[Col 3],
e.[Col 4] AS [Col 4],
p.[Col 4] AS [Col 5],
FROM p INNER JOIN
e ON (p.[Col 3] = E.[Col 3] and p.[Col 2] = e.[Col 2] and p.[Col 1] = e.[Col 1])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论