Geek Answers Handbook

Selecting more than one column in a subquery

I have a query that selects the oldest record from table B, which contains several rows for each row of table A and connects it to table A:

SELECT A.Surname, A.Fornames, A.DOB, 
(SELECT TOP 1 B.RegNumber
   FROM B
   WHERE B.ID=A.ID 
   ORDER BY B.Date ASC) AS RegNumber
FROM A
ORDER BY A.Surname;

This works fine, however, I also want to pull another column from table B. So I would have:

A.Surname, A.Fornames, A.DOB, B.RegNumber, B.RegDate

How can I do this while still getting the oldest record from table B?

+4
source share
3 answers

You have two options: the first is to restructure the request using joins:

Principle: you need to get the first date for each entry in B, grouped by ID:

SELECT  ID, MIN(Date) AS [FirstDate]
FROM    B
GROUP BY ID;

You can then attach this back to B to filter the results, i.e.:

SELECT  B.*
FROM    B
        INNER JOIN
        (   SELECT  ID, MIN(Date) AS [FirstDate]
            FROM    B
            GROUP BY ID
        ) AS B2
            ON B2.ID = B.ID
            AND B2.FirstDate = B.Date;;

A :

SELECT  A.Surname, A.Fornames, A.DOB, B.RegNumber, B.RegDate
FROM    (A
        INNER JOIN B
            ON B.ID = A.ID)
        INNER JOIN
        (   SELECT  ID, MIN(Date) AS [FirstDate]
            FROM    B
            GROUP BY ID
        ) AS B2
            ON B2.ID = B.ID
            AND B2.FirstDate = B.Date
ORDER BY A.Surname;

JOINs:

SELECT  A.Surname, A.Fornames, A.DOB, B.RegNumber, B.RegDate
FROM    (A
        INNER JOIN B
            ON A.ID = B.ID)
        LEFT JOIN B AS B2
            ON B2.ID = B.ID
            AND B2.Date < B.Date
WHERE   B2.ID IS NULL
ORDER BY A.Surname;

, B , (B2) , (B), , B2.ID , , B, .

- :

SELECT A.Surname, A.Fornames, A.DOB, 
    (SELECT TOP 1 B.RegNumber
        FROM B
        WHERE B.ID=A.ID 
        ORDER BY B.Date ASC) AS RegNumber
    (SELECT TOP 1 B.RegDate
        FROM B
        WHERE B.ID=A.ID 
        ORDER BY B.Date ASC) AS RegDate
FROM A
ORDER BY A.Surname;

, , B , JOINs , . , B, , , .

+2

:

SELECT A.Surname, A.Fornames, A.DOB, 
       (SELECT TOP 1 B.RegNumber
        FROM B
        WHERE B.ID=A.ID 
        ORDER BY B.Date ASC
       ) AS RegNumber,
       (SELECT min(b.Date)
        FROM B
        WHERE B.ID=A.ID 
       ) AS RegDate
FROM A
ORDER BY A.Surname;
+3

...

SELECT A.Surname, A.Fornames, A.DOB, B.RegNumber, B.RegDate
FROM A
JOIN (
    Select top 1 RegNumber, RegDate
    From B
    WHERE B.ID = A.ID
    Order By B.Date ASC
) B on A.ID = B.ID
ORDER BY A.Surname;

, - . "id" . , , , .

SELECT A.Surname, A.Fornames, A.DOB, MIN(B.RegNumber) As RegNumber, MIN(B.RegDate) as RegDate
FROM A
JOIN (
    Select ID, RegNumber, RegDate
    From B
) B on A.ID = B.ID
Group By A.Surname, A.Fornames, A.DOB
ORDER BY A.Surname;
-2

More articles:


All Articles