I have the following master table
.------------------.
| id | parent_name |
.------------------.
| 1 | Mike |
| 2 | Sarah |
| 3 | Danial |
| 4 | Alex |
.------------------.
And have the following child-table details:
.------------------------------------------.
| id | parent_id | child_name | birth year |
.------------------------------------------.
| 1 | 1 | mandy | 2000 |
| 2 | 1 | mark | 2003 |
| 3 | 1 | mathew | 2005 |
| 4 | 2 | sandy | 1998 |
| 5 | 2 | sharon | 2006 |
| 6 | 3 | david | 2001 |
.------------------------------------------.
In the example above, I delibretaly choose names of children with the first letter matching their parents' names just to make it easier to understand the relationship, even though each child is connected to his/her parent using the parent_id
.
What I would like to have is a list of all parents (4 rows) and to have a matching 4 rows from the children table, selecting only the last born child of each respectful parent.
.-------------------------------.
| id | parent_name | last_child |
.-------------------------------.
| 1 | Mike | mathew |
| 2 | Sarah | sharon |
| 3 | Danial | david |
| 4 | Alex | (null) |
.-------------------------------.
In oracle, this is easy:
SELECT
p.id,
p.parent_name,
c.child_name last_child
FROM
parents_table p,
children_table c
WHERE
p.id = c.parent_id
AND c.birth_year = (SELECT MAX(birth_year) FROM children_table where parent_id = p.id)
But I am struggling to generate the same result in MS Access.. MS Access does not accept sub-queries (for select the child having the maximum birth year for the same parent).
Is there a better way to get the result in MS Access?
Access certainly does support subqueries, but you're using a crossjoin, so you will never get a null there.
Instead, left join and perform a subquery in the FROM
clause.
Your query would fail identically in Oracle, by the way. There are no relevant differences between Access and Oracle here.
SELECT
p.id,
p.parent_name,
c.child_name last_child
FROM
parents_table p
LEFT JOIN
(
SELECT *
FROM children_table c
WHERE c.birth_year = (SELECT MAX(c2.birth_year) FROM children_table c2 WHERE c2.parent_id = c.parent_id)
) c
ON p.id = c.parent_id
Access sometimes performs better with an EXISTS
, so a rewrite to that would be:
SELECT
p.id,
p.parent_name,
c.child_name last_child
FROM
parents_table p
LEFT JOIN
(
SELECT *
FROM children_table c
WHERE EXISTS(SELECT 1 FROM children_table c2 WHERE c2.parent_id = c.parent_id HAVING c.birth_year = MAX(c2.birth_year))
) c
ON p.id = c.parent_id