时间戳

首页 > 解决方案 > Access sql查询连接匹配有两个匹配的连接行

sql - Access sql查询连接匹配有两个匹配的连接行

问题描述

给定表格：

tblGuardian
| ID | FirstName | LastName |
|----|-----------|----------|
|  1 |       Sam |    Smith |
|  2 |      John |    Jones |
|  3 |      Jack |    Black |
|  4 |      Jane |      Doe |

tblChild
| ID | FirstName | LastName | Sex |                  DOB | GuardianID |
|----|-----------|----------|-----|----------------------|------------|
|  1 |      Sara |    Smith |   F | 2010-01-01T00:00:00Z |          1 |
|  2 |      Dave |    Smith |   M | 2008-03-01T00:00:00Z |          1 |
|  3 |      Mike |    Jones |   M | 2009-06-01T00:00:00Z |          2 |
|  4 |      Fred |    Jones |   M | 2010-07-01T00:00:00Z |          2 |
|  5 |     Sally |    Black |   F | 2011-11-01T00:00:00Z |          3 |
|  6 |     Harry |      Doe |   M | 2007-07-01T00:00:00Z |          4 |
|  7 |      Kate |      Doe |   F | 2008-04-01T00:00:00Z |          4 |

Access sql 查询将返回什么：

  1. 所有监护人及其子女的出生日期
  2. 至少有一个女孩和一个男孩

我希望结果看起来像：

| Firstname | LastName | Child FirstName |       DOB  |
|-----------|----------|-----------------|------------|
|       Sam |    Smith |            Sara | 2010-01-01 |
|       Sam |    Smith |            Dave | 2008-03-01 |
|      Jane |      Doe |           Harry | 2007-07-01 |
|      Jane |      Doe |            Kate | 2008-04-01 |

我有这个有效的 MS SQL 查询：

SELECT g1.FirstName, g1.LastName, c1.FirstName, c1.DOB FROM
tblGuardian g1 INNER JOIN tblChild c1 ON g1.ID = c1.GuardianID
WHERE g1.ID in (
SELECT g.ID
FROM tblGuardian g INNER JOIN tblChild c ON c.GuardianID = g.ID
GROUP BY g.ID
HAVING Count(Distinct c.Sex) = 2
)

我现在需要将其转换为 MS Access！

Access SQL 不接受 Count(Distinct ...)。

标签： sqlms-access

解决方案

我认为这两个数据库中最好的方法看起来更像这样：

SELECT g.FirstName, g.LastName, c.FirstName, c.DOB
FROM tblGuardian as g INNER JOIN
     tblChild as c
     ON g.ID = c.GuardianID
WHERE EXISTS (SELECT 1 FROM tblChild as c2 WHERE c2.GuardianID = g.ID AND c2.Sex = "F") AND
      EXISTS (SELECT 1 FROM tblChild as c2 WHERE c2.GuardianID = g.ID AND c2.Sex = "M");

除了日期常量的引号外，此特定版本将适用于两个数据库。

推荐阅读