从子表中为父表中的每一行选择单行 [英] SELECT single row from child table for each row in parent table

问题描述

我正在尝试从子表中仅为每个包含行子字段的父行获得一行，我一直在尝试使用GRUOP BY，但没有成功:( 这是我最初的选择

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

这是桌子的结构

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

推荐答案

从子表中仅为包含父字段的每个父行获取一行"

这听起来像child表对于同一pID值可以有多行.而且，每个pID只希望有一个child行.

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;

再次加入该GROUP BY查询回到child表，以获取每个目标cID值的其他列.将此查询另存为qryChild.

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;

最后，要包含lastname值，请将parent表连接到qryChild.

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

这篇关于从子表中为父表中的每一行选择单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！