join one to many code example

Example 1: sql join one to many

SELECT   *
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId

Example 2: sql join one to many

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

Example 3: how to join tables with many to many relationships

SELECT 
    p.person_id, 
    p.name, 
    a.conference, 
    b.publication
FROM
    person AS p
    LEFT JOIN (SELECT
                   pc.person_id,
                   c.conference
               FROM
                   person_conference AS pc
                   INNER JOIN conference AS c 
                     ON pc.conference_id = c.conference_id) AS a
      ON p.person_id = a.person_id 
    LEFT JOIN (SELECT
                   pp.person_id,
                   ppp.publication
               FROM
                   person_publication AS pp
                   INNER JOIN publication AS ppp 
                     ON pp.publication_id = ppp.publication_id) AS b
      ON p.person_id = b.person_id

Example 4: sqlalchemy query join many to many

session.query(User).join((Group, User.groups)) \
    .join((Department, Group.departments)).filter(Department.name == 'R&D')