11

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

2 Answers 2

9

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
3
  • Thanks ypercube for your perceptiveness, I changed the GROUP BY. Oct 21, 2015 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 Oct 21, 2015 at 10:01
  • How we can have list of result? I mean wanna have result that every item has list of it's one to many relations.
    – Mahdi
    Mar 23, 2022 at 6:04
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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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