SELECT single row from child table for each row in parent table

ms-access, sql

Solution

"get only one row from child table for each parent row with child fields included"

That sounds like the `child` table can have more than one row for the same `pID` value. And you want only one `child` row for each `pID`.

SELECT pID, Min(cID) AS MinOfcID
FROM child
GROUP BY pID;

Join that `GROUP BY` query back to the `child` table again to retrieve the other columns for each target `cID` value. Save this query as `qryChild`.

SELECT
    c.pID,
    c.cID,
    c.phone,
    c.company,
    c.title,
    c.address
FROM
    (
        SELECT pID, Min(cID) AS MinOfcID
        FROM child
        GROUP BY pID
    ) AS map
    INNER JOIN child AS c
    ON c.cID = map.MinOfcID;

Finally, to include `lastname` values, join the `parent` table to `qryChild`.

Problem

I am trying to get only one row from child table for each parent row with child fields included, I have been trying with GRUOP BY but with no success :( Here is my initial SELECT ``` SELECT pID, lastname FROM parent LEFT JOIN (SELECT cID, pID, phone, company, title FROM child) as child ON parent.pID = child.pID ``` Here is the tables strcture ``` CREATE TABLE parent ( pID Counter(1,1) PRIMARY KEY, firstname VarChar(24) DEFAULT '', lastname VarChar(20) DEFAULT '' ); CREATE TABLE child ( cID Counter(1,1) PRIMARY KEY, pID int DEFAULT '0', phone VarChar(16) DEFAULT '', company VarChar(24) DEFAULT '', title VarChar(24) DEFAULT '', address TEXT ); ```

Original source