因此，看起来您希望每个表中的所有记录都是相同的，然后只希望每个表中的记录都是不同的。这意味着您需要UNIOn 3组查询。
尝试这样的事情：
SELECt t1.state, t1.lname, t1.fname, t1.network as t1Network, t2.network as t2NetworkFROM table1 t1 INNER JOIN table2 t2 ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state AND t1.network=t2.networkUNIOn SELECt t1.state, t1.lname, t1.fname, t1.network as t1Network, t2.network as t2NetworkFROM table1 t1 LEFT JOIN table2 t2 ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state AND t1.network=t2.networkWHERe t2.network IS NULLUNIOn SELECt t2.state, t2.lname, t2.fname, t1.network as t1Network, t2.network as t2NetworkFROM table2 t2 LEFT JOIN table1 t1 ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state AND t1.network=t2.networkWHERe t1.network IS NULL
这应该给您您想要的结果。
这是要确认的SQL Fiddle。
- 编辑
今天不考虑-您实际上不需要第一个查询。您可以从第二个查询中删除WHERe条件，其工作方式相同。疲劳的 ：-）
这是更新后的查询-两者都应该正常工作，这更容易阅读：
SELECt t1.state, t1.lname, t1.fname, t1.network as t1Network, t2.network as t2NetworkFROM table1 t1 LEFT JOIN table2 t2 ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state AND t1.network=t2.networkUNIOn SELECt t2.state, t2.lname, t2.fname, t1.network as t1Network, t2.network as t2NetworkFROM table2 t2 LEFT JOIN table1 t1 ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state AND t1.network=t2.networkWHERe t1.network IS NULL
和更新的小提琴。
顺便说一句-它们都应该在MSAccess中都支持
UNIOn。
祝你好运。
