SQL JOIN和LEFT在不同的条件下加入同一个表
问题描述:
当满足某些条件时,我需要加入一个表,并且在满足其他条件时LEFT加入它。由于我在MS CRM动态与过滤器,我不能使用UNIONSQL JOIN和LEFT在不同的条件下加入同一个表
SELECT stuff.stuffs
FROM MainTable
JOINS othertable
LEFT JOIN othertables
LEFT JOIN TableX as X1 ON (Conditions1.1 = X1.1
AND MainTable.Type = 1)
JOIN TableX as X2 ON (Conditions2.2 = X2.2
AND MainTable.Type = 2)
- 如果我注释掉X2部分,我得到了我从X1的部分并没有什么从X2需要。
- 如果我左加入X2部分,我在X2中有额外的信息。
- 如果我这样离开它,我会从X2部分得到我需要的,但从X1中得不到什么。
我尝试了几个变体,但我无法达到满意的解决方案。
编辑:我原来的查询是这样:
SELECT stuff.stuffs
FROM MainTable
(LEFT) JOIN TableX as X1
ON (Conditions1.1 = X1.1
AND MainTable.Type = 1)
OR
ON (Conditions2.2 = X2.2
AND MainTable.Type = 2)
但如果是作为左加入,我从X2 获得额外的信息和JOIN给我从X1缺少信息,
答
实际需要的匹配是不是在OP清楚,但你可以尝试这样的:
Select stuff.stuffs
From MainTable
Left Join TableX As X1
On (MainTable.Type = 1 And X1.1 = ...)
Or (MainTable.Type = 2 And X1.2 = ...)
鉴于您添加到OP中的内容以及注释中,是否从TableX中查找满足条件或仅满足条件之一的行仍不清楚。不过,为了完整起见,这里是两个:
任一情况:我上面的原始解决方案和您添加到您的文章的解决方案。
一个也是唯一的一个条件:
Select stuff.stuffs
From MainTable
Left Join TableX As X1
On X1.1 = ...
Left Join TableX As X2
On X2.2 = ...
Where (MainTable.Type = 1 And X1.PK Is Not Null And X2.PK Is Null)
Or (MainTable.Type = 2 And X2.PK Is Not Null And X1.PK Is Null)
什么是从原来的职位缺少的是外键引用从TableX的到MainTable的任何概念。因此,通常我会想到类似于:
Select stuff.stuffs
From MainTable
Left Join TableX As X1
On X1.FK = MainTable.PK
Left Join TableX As X2
On X2.FK = MainTable.PK
Where (MainTable.Type = 1 And X1.Col1 = ... And X2.PK Is Null)
Or (MainTable.Type = 2 And X2.Col1 = ... And X1.PK Is Null)
答
我“M想你需要自行试试这个:
SELECT stuff.stuffs
FROM TABLEX x
LEFT JOIN MAINTABLE mt1 ON mt.1 = x.1
AND mt.type = 1
LEFT JOIN MAINTABLE mt2 ON mt.2 = x.2
AND mt.type = 2
我们能否看到最终查询?你需要的东西仍然不清楚。 – ErikE 2010-07-22 01:53:16