7

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

7

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
2
  • Thanks ypercube for your perceptiveness, I changed the GROUP BY. – Peter Elzinga Oct 21 '15 at 8:57
  • Depending on the DBMS and the data distribution the most effective way might be to group the child first and then join on the grouped result – a_horse_with_no_name Oct 21 '15 at 10:01
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.