有人能帮我解决以下查询吗?
我们有: 1. 左外连接 2. 两个右外连接
如何同时使用左和右O.J并产生与上述相同的效果,
我需要重写相同的查询,因为我正在将我们的旧ms SQL2009转换为ms SQL2012。在2012年,*=
,=*
不受支持。
SELECT X.master_key, X.vendor_code
FROM
X, Y, X x2
WHERE Y.master_key = X.parent_key
AND Y.master_key *= x2.parent_key
AND x2.INITIALS =* Y.DEFAULT_INITIALS
AND x2.VENDOR_CODE =* Y.VENDOR_ABBREV
AND Y.project_name = 'TEST'
我用我的基础知识修改了上面的代码,如下所示,但根本不起作用。
SELECT X.master_key, X.vendor_code
FROM
X,
Y left outer join X x2
on Y.master_key = x2.parent_key,
X vnd RIGHT OUTER JOIN Y vnm
(on vnd.INITIALS = vnm.DEFAULT_INITIALS AND vnd.VENDOR_CODE = vnm.VENDOR_ABBREV )
WHERE Y.master_key = X.parent_key
AND Y.project_name = 'TEST'
我没有得到原始查询所期望的值。
以下是我的完整原始查询。
SELECT vnd.master_key, vnd.vendor_code
FROM
vnd, vnm, vnd vn2
WHERE vnm.master_key = vnd.parent_key
AND vnm.master_key *= vn2.parent_key
AND vn2.INITIALS =* vnm.DEFAULT_INITIALS
AND vn2.VENDOR_CODE =* vnm.VENDOR_ABBREV
AND vnm.inactive = 0
AND vnd.inactive = 0
AND vnm.project_name = 'TEST'
ORDER BY
lower(vnm.company_name + ' '), lower(vnd.vendor_code) ,lower(vnd.first_name + ' '), lower(vnd.last_name + ' ')
您提供的解决方案可行,但您能否也提供上述方案呢?
SELECT X.master_key, X.vendor_code
FROM X
INNER JOIN Y ON X.parent_key = Y.master_key
LEFT OUTER JOIN X AS x2 ON x2.parent_key = Y.master_key AND x2.INITIALS = Y.DEFAULT_INITIALS AND x2.VENDOR_CODE = Y.VENDOR_ABBREV
WHERE Y.project_name = 'TEST'
致敬
Janaki