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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityMost 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
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.