CODEWITHSUNDEEP
sqlms-access
Walter
Walter

Reputation: 181

Access sql query join match have two matching joined rows

Given Tables:

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 |

What Access sql query would return:

  1. all guardians and the DOBs of their children
  2. that have at least one female and one male child

I would like the result to look like:

| 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 |

I have this MS SQL query that works :

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
)

I now need to convert that to MS Access!

The Access SQL doesn't accept Count(Distinct ...).

Upvotes: 0

Views: 21

Answers (3)

LJ01
LJ01

Reputation: 589

You should be able to change your logic to something along the lines of

Sum (case when male then 1 else 0) > 0
and 
Sum (Case when female then 1 else 0) > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I think the best approach in both databases looks more like this:

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");

This particular version will work on both databases, except for the quotes around the date constants.

Upvotes: 1

Walter
Walter

Reputation: 181

Thanks to Pham X. Bach the solution that worked is:

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 SUM(IIF(c.Sex="M",1,0))>0 AND SUM(IIF(c.Sex="F",1,0))>0
)

Upvotes: 0

Related Questions