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

Question

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

Answer 1

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