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 community
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
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 JOINclause to join both tables.