我试图找出如何执行JOIN的方法,其中我从以下结果集(查询1)返回所有记录a.ssan = c.ssn
:
查询1:
SELECT
a.memno,
a.name,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.sex,
a.lname,
a.ssan,
b.addr1 as old_addr1,
b.addr2 as old_addr2,
b.city as old_city,
b.state as old_state,
b.zip as old_zip,
b.timec
FROM Lib1.Table1 a, Lib1.Table2 b
WHERE
a.memno = b.memno and
b.groupid = 'P2' and
b.type = 'B' and
b.datec = 20131206 AND
(a.addr1 <> b.addr1 or
a.addr2 <> b.addr2 or
a.city <> b.city or
a. state <> b.state or
a.zip <> b.zip)
表C字段:
SSN
DATEC
TIMEC
PRINT_OLD
PRINT_NEW
我正在为新地址和旧地址生成邮件合并文档,以提醒成员其帐户上的地址更改。制作新的地址信函时,我会在TABLEC
设置除以外的所有字段的值时运行插入PRINT_OLD
。示例数据为:
SSN: 123456789;
DATEC: 20131206;
TIMEC: 133000;
PRINT_NEW: 2013-12-06-13.30.00.965000;
PRINT_OLD: NULL;
然后几天后,当要打印OLD地址字母时,我需要运行上面的第一个查询并结合一个JOIN
on TTABLEC
来显示那些PRINT_OLD字段为NULL的记录,表示当天已经打印了NEW Address字母。 ,但OLD Address Letter没有。
谁能帮我解决这个问题?在数据库查询方面,我并不是最强的。
你可以做:
SELECT *
FROM tableC c
INNER JOIN (
SELECT a.memno,
a.NAME,
a.addr1,
a.addr2,
a.city,
a.STATE,
a.zip,
a.sex,
a.lname,
a.ssan,
b.addr1 AS old_addr1,
b.addr2 AS old_addr2,
b.city AS old_city,
b.STATE AS old_state,
b.zip AS old_zip,
b.timec
FROM Lib1.Table1 a
INNER JOIN Lib1.Table2 b ON a.memno = b.memno
WHERE b.groupid = 'P2'
AND b.type = 'B' AND b.datec = 20131206
AND (
a.addr1 <> b.addr1 OR a.addr2 <> b.addr2
OR a.city <> b.city OR a.STATE <> b.STATE
OR a.zip <> b.zip)
) a ON a.ssan = c.ssn
WHERE c.print_old IS NULL
注意:我还将您的JOIN更改为显式的INNER JOIN。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句