Geek asks and answers

SELECT one row from the child table for each row in the parent table

I am trying to get only one row from a child table for each parent row with child fields enabled, I tried with GRUOP BY, but without success :( Here is my original 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 strcture table

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
);
+3
source share
4 answers

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

It looks like a table childcan have more than one row for the same value pID. And you want only one line childfor each pID.

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

GROUP BY child, cID. 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.

+5

, ANSI:

SELECT pID, lastname 
FROM parent 
  LEFT JOIN (
         SELECT pID, 
                row_number() over (partition by pid order by cid) as rn
         FROM child
         ) as child 
         ON parent.pID = child.pID and child.rn = 1

, "" , . , "" , - , order by cid . , - "" , .

Btw: , .

+3

you will need to replace

SELECT cID, pID, phone, company, title FROM child

with something returning only one row per pid. Which one is right for you. Symbols group by:

SELECT min(cID), pID, min(phone), min(company), min(title) FROM child group by cID

but different columns will come from different rows, so you probably want something like the "first row", how to implement, which depends on the DBMS used, so please add this information to your question.

0
source 
SELECT cID ChildID, 
       pID ParentID, 
       phone, 
       company, 
       title, 
       (SELECT lastname FROM parent WHERE id = ParentID) as LastName
FROM child
GROUP BY ParentID
0
source

More articles:


All Articles