首页 文章

从一个表中选择所有记录，从另一个表中选择特定记录

提问于
浏览 次
-1

我有三张 table .

Table1(Table1Id, Field1, Field2)
Table2(Table2Id, Table1Id, AuditDate, Field1, Field2)
Table3(Table3Id, Table1Id, AuditDate, Field1, Field2)

我想要做的是选择Table1中的所有记录和字段，查看Table2和Table3以获得匹配的Table1Id，并从Table3中选择具有最新AuditDate的记录 . 我觉得我需要进行类似的查询 .

SELECT DISTINCT Table1.Table1Id, Table1.Field1, Table1.Field2,
               Table2.Field1, Table2.Field2,
               Table3.Field1, Table3.Field2
FROM (Table1 
INNER JOIN Table2 ON Table1.Table1Id = Table2.Table2Id)
INNER JOIN Table3 ON Table1.Table1Id = Table3.Table3Id
WHERE Table3.AuditDate =
    (SELECT MAX(AuditDate) FROM Table3
     WHERE Table1.Table1Id = Table3.Table3Id)

这半成品 . 例如，如果Table3中没有任何与Table1相关的记录，则不会返回任何内容 . 我需要确保创建Table1中每条记录的记录 .

sql ms-access join

4 回答

  • 0

    它应该是这样的查询：

    SELECT t1.field1..t1.fieldN, t2.field1 ... t2.fieldN, t3.field1 ... t3.fieldN
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id=t2.id
JOIN table3 AS t3 ON t2.id=t3.id
    回复于
  • 0

    这可能对你有帮助，

    select Table1Id,field1,field2 from Table1,Table2,Table3 
    where 
        Table1.Table1Id=Table2.Table2Id and 
        Table1.Table1Id=Table3.Table2Id and 
        Table2.AuditDate=Table3.AuditDate 
    group by Table1Id,field1,field2 
    having Table2.AuditDate = (
        Select max(AuditDate) from Table1,Table2,Table3 
            where  
                Table1.Table1Id=Table2.Table2Id and 
                Table1.Table1Id=Table3.Table2Id and  
                Table2.AuditDate=Table3.AuditDate 
            group by Table2.AuditDate
    );
    回复于
  • -2

    听起来你想要原始查询的结果，加上表1中没有相应记录的表3中的记录（或表2） . 这可以通过将 INNER JOIN 替换为 LEFT JOIN 并将 WHILE 子句转换为 ON 子句来获得 . 然后，您将获得此查询，即使在其他表中找不到相应的记录，也将返回Table1中的记录 .

    SELECT DISTINCT Table1.Table1Id, Table1.Field1, Table1.Field2,
               Table2.Field1, Table2.Field2,
               Table3.Field1, Table3.Field2
FROM (Table1 LEFT JOIN Table2
ON Table1.Table1Id = Table2.Table2Id)
LEFT JOIN Table3
ON Table1.Table1Id = Table3.Table3Id
AND Table3.AuditDate =
    (SELECT MAX(AuditDate) FROM Table3
     WHERE Table1.Table1Id = Table3.Table3Id)
    回复于
  • 1

    尝试

    SELECT * FROM Table1 AS T1
    JOIN Table2 AS T2 ON T1.Table1Id = T2.Table1Id 
    JOIN Table3 AS T3 ON T1.Table1Id = T3.Table1Id 
    GROUP BY T2.AuditDate
    回复于

相关问题