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 communityThis 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
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 JOIN
clause to join both tables.