Efficient way to join two tables with one to many relationship

Question

This is interview question asked to me in interview and i was not able to answer. Please help..

What is the most efficient way to join two table with one to many relationship between them

Answer 1

Most effective way is to use an INNER JOIN, like this:

SELECT   *
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId

And when you need aggregates on child level, you can add a GROUP BY and SUM and/or AVG or other aggregate functions:

SELECT      P.Name
            , P.Address
            , SUM(C.Amount)
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId
GROUP BY    P.Name
            , P.Address

Answer 2

Since the question explicitly states "with a one-to-many relationship", perhaps they are asking you to explain primary-key and foreign-key relationships.

In this case, you might say:

The table on the "one" side of the "one-to-many" relationship should have a primary key column. The other table should have a foreign-key defined pointing to the primary key on the first table. To return results from both tables you'd add an INNER JOIN clause to join both tables.