Join tables multiple times on different rows in one result set

Question

So I've got 2 tables (simplified below)

members                     documents
------------                ------------------
id | name | registered      id | member_id | type | expiry
----------------------      ------------------------------
1  | AAA  | 1234567890      1  | 1         | 1    | 1234567890
2  | BBB  | 1234567890      2  | 1         | 2    | 1234567891
3  | CCC  | 1234567890      3  | 1         | 3    | 1234567892
                            4  | 2         | 1    | 1234567893
                            5  | 2         | 2    | 1234567894
                            6  | 2         | 3    | 1234567890

and I need to display these like this:

member id | name | doc 1 expiry | doc 2 expiry | doc 3 expiry 
--------------------------------------------------------------
1         | AAA  | 1234567890   | 1234567891   | 1234567892
2         | BBB  | 1234567893   | 1234567894   | 1234567895

I've tried querying with multiple outer joins and aliases but it's just repeating the document expiry timestamps. This is what I have so far:

    SELECT DISTINCT `members`.`id`, `members`.`name`, `a`.`expiry` AS `expiry1`, `b`.`expiry` AS `expiry2`, `c`.`expiry` AS `expiry3` 
    FROM `members` 
    LEFT OUTER JOIN `documents` a ON `a`.`member_id` = `members`.`id` 
    LEFT OUTER JOIN `documents` b ON `b`.`member_id` = `members`.`id` 
    LEFT OUTER JOIN `documents` c ON `c`.`member_id` = `members`.`id` 
    GROUP BY `members`.`id`

People need to be able to search through this, for example to list everyone whose document type 3 has expired.

Zane Bien · Accepted Answer · 2012-06-14T03:50:54.263

Try

SELECT 
    a.id AS 'member id',
    a.name
    SUM(a.d1exp) AS 'doc 1 expiry',
    SUM(a.d2exp) AS 'doc 2 expiry',
    SUM(a.d3exp) AS 'doc 3 expiry'
FROM
    (
        SELECT 
            aa.id,
            aa.name,
            COALESCE(d1.expiry, 0) AS d1exp,
            COALESCE(d2.expiry, 0) AS d2exp,
            COALESCE(d3.expiry, 0) AS d3exp
        FROM
            members aa
        LEFT JOIN
            documents d1 ON aa.id = d1.member_id AND d1.type = 1
        LEFT JOIN
            documents d2 ON aa.id = d2.member_id AND d2.type = 2
        LEFT JOIN
            documents d3 ON aa.id = d3.member_id AND d3.type = 3
    ) a
GROUP BY
    a.id,
    a.name

This is assuming the values in the 'expiry' field are numerical.

Thanks! I don't quite know what's going on there so I'm going to read up on this but it's done the trick! — Andrew Martin, Jun 14 '12 at 04:04

Join tables multiple times on different rows in one result set

1 Answers1