SQL need most efficient way to select items list with sublists?

Question

Lets look at some very simple example, have 3 tables:

• dbo.Person(PersonId, Name, Surname)
• dbo.Pet(PetId, Name, Breed)
• dbo.PersonPet(PersonPetId, PersonId, PetId)

Need to select all persons with theirs pets if person has any.... for ex. in final application it should look smth like:

whats the most efficient way:

1. Select all persons and then in data access layer fill each person pets list with separate select?
2. Use join in sql level and then in data access layer filter all persons duplicates, by adding only one to result list and from other just filling pet list?
3. any other ideas?

Answer 1

The most efficient way is to select them all at once:

select p.*, pt.*
from Person p
left outer join PersonPet pp on p.PersonId = pp.PersonId
left outer join Pet pt on pp.PetId = pt.PetId

Answer 2

Need to select all persons with theirs pets if person has any...

Use:

   SELECT per.name,
          per.surname,
          pt.name
     FROM dbo.PERSON per
LEFT JOIN dbo.PERSONPET perpet ON perpet.personid = per.personid
     JOIN dbo.PETS pt ON pt.petid = perpet.petid

Answer 3

Personally I would do it as a stored proc on the sql server. Whichever way you do it though, for display purposes you're going to have to filter out the duplicate Name and Surname.

Answer 4

The majority of the time taken to retrieve records is spent setting up and tearing down a query to the database. It doesn't make much difference how much data or how many tables you use in the query. It will be much more efficient to use a single query to get all the data. If your data access layer fetches each separately you'll get poor speed. Definitely use a join.

Answer 5

If your client and back end support multiple result sets you could also do somthing like (assuming its MSSQL)

Create Proc usp_GetPeopleAndPets 
AS
BEGIN
    SELECT PersonId, Name, Surname
    FROM 
    dbo.Person p;

    SELECT 
        pp.PersonID,
        p.PetId, p.Name, p.Breed
    FROM
    dbo.PersonPet pp
    INNER JOIN dbo.Pet p
    ON pp.PetId = p.PetId
    Order BY pp.PersonId
END

The data retrieval time would be roughly equivalent since its one trip to the DB. Some clients support relationships between results which allow you to do something like Person.GetPets() or Pet.GetPerson()

The main advantage to this approach is that if you're working with people you don't have to worry about the "filter[ing] all person duplicate[s]"