Group results from two tables

Question

I want to group the results of the following two tables

program_outcome

+----------------------+-------------------+------------+-------------+
|  program_outcome_pk  |  program_outcome  |  modified  | modified by |
+----------------------+-------------------+------------+-------------+

accreditation_standard

+-----------------------------+--------------------------+------------+-------------+
|  accreditation_standard_pk  |  accreditation_standard  |  modified  | modified by |
+-----------------------------+--------------------------+------------+-------------+

using the following junction/lookup table:

program_outcome_accreditation_standard_lookup

+----------------------------------------------------+----------------------+----------------------------+
|  program_outcome_accreditation_standard_lookup_pk  |  program_outcome_fk  |  accreditation_standard_fk | 
+----------------------------------------------------+----------------------+----------------------------+

Results should look like:

+-------------------+--------------------------+
| program_outcome 1 |  accreditation_standard 2 |
|                   |  accreditation_standard 5 |
|                   |  accreditation_standard 9 |
|                   |  ...                      |
+-------------------+--------------------------+
| program_outcome 2 |  accreditation_standard 3 |
|                   |  accreditation_standard 7 |
|                   |  accreditation_standard 8 |
|                   |  ...                      |
+-------------------+--------------------------+

and so on..

So far I have tried:

SELECT
    * 
FROM
    program_outcome,
    accreditation_standard,
    program_outcome_accreditation_standard_lookup
    LEFT JOIN accreditation_standard a_s ON a_s.accreditation_standard_pk = program_outcome_accreditation_standard_lookup.accreditation_standard_fk
    LEFT JOIN program_outcome p_o ON p_o.program_outcome_pk = program_outcome_accreditation_standard_lookup.program_outcome_fk 
GROUP BY
    p_o.program_outcome_pk

But the above query is not grouping accreditation_standard.accreditation_standard_fk according to program_outcome.program_outcome_fk in the above junction/lookup table.

I probably should be using a GROUP_CONCAT but not sure how to implement in the above. I am using MySQL v5.5

See Dbfiddle dbfiddle

I am formatting the results as follows:

$result = $connection->query( $query );

echo "<table width='100%' border='1'><thead><tr><th>Program Outcomes</th><th>Accreditation Standards</th></tr></thead><tbody>";
while ($row = mysqli_fetch_array($result)) {
echo "<tr><td>" . $row['program_outcome'] . "</td><td>" . $row['accreditation_standard']  . "</td></tr>";
}
echo "<tbody></table>";

No, I can format the results using PHP and HTML...see updated OP — , Jul 30 '20 at 02:56
I think you can simplify your example but if I understand it correctly, you are trying to do pivot, [can this help?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — FanoFN, Jul 30 '20 at 02:59
The results shown in the question is illustrative only, that actual results are determined by the lookup table. — , Jul 30 '20 at 02:59
`GROUP BY` means you get one row for each group. If you want them on consecutive rows, use `ORDER BY` — Barmar, Jul 30 '20 at 03:00
It's interesting that you're using comma-join and `LEFT JOIN` .. any particular reason why? Or it's just experimental? — FanoFN, Jul 30 '20 at 03:05
Do you understand that comma has lower precedence than keyword joins? So mixing them can be confusing. (Did you add a 2nd join with program_outcome because you got unknown-column errors? Because the 2nd comma join is done after the keyword joins & the 1st program_outcome & its columns aren't known in the keyword joins.) Also always using (short mnemonic) table aliases makes it easy to know what table a column is from. — philipxy, Jul 30 '20 at 03:50
Thanks for the info and advice, I will definitely look at that in future. I have now put the final solution in a separate answer instead of in the OP. — , Jul 30 '20 at 04:10
And for next time, see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query). By no stretch of the imagination can the present data set be considered minimal. — Strawberry, Jul 30 '20 at 08:47

score 1 · Answer 1 · answered Jul 30 '20 at 03:02

I am not sure whether the mapping is correct, you can modify it yourself.

SELECT
    CONCAT( 'program_outcome ', p_o.program_outcome_pk ) AS popk,
    GROUP_CONCAT( DISTINCT CONCAT( 'accreditation_standard  ', a_s.accreditation_standard_pk ) SEPARATOR "\r\n" ) AS aspk 
FROM
    program_outcome,
    accreditation_standard,
    program_outcome_accreditation_standard_lookup
    LEFT JOIN accreditation_standard a_s ON a_s.accreditation_standard_pk = program_outcome_accreditation_standard_lookup.accreditation_standard_fk
    LEFT JOIN program_outcome p_o ON p_o.program_outcome_pk = program_outcome_accreditation_standard_lookup.program_outcome_fk 
GROUP BY
    p_o.program_outcome_pk

score 1 · Answer 2 · answered Jul 30 '20 at 04:09

This is the final working solution from the first two answers offered.

  $query = "SELECT program_outcome, 
    GROUP_CONCAT(accreditation_standard SEPARATOR '|') AS accreditation_standards
    FROM program_outcome p
    JOIN program_outcome_accreditation_standard_lookup l ON p.program_outcome_pk = l.program_outcome_fk
    JOIN accreditation_standard a ON l.accreditation_standard_fk = a.accreditation_standard_pk
    GROUP BY program_outcome";
    
        $result = $connection->query( $query );
        
        echo "<table width='100%' border='1'><thead><tr><th>Program Outcomes</th><th>Accreditation Standards</th></tr></thead><tbody>";
        while ($row = mysqli_fetch_array($result)) {
        echo "<tr><td>" . $row['program_outcome'] . "</td><td>" . str_replace('|', '<p>',$row['accreditation_standards']) . "</td></tr>";
        }
        echo "<tbody></table>";

I also set the MAX_LEN before the above query to increase the number of results returned without being truncated...

$query = 'SET GROUP_CONCAT_MAX_LEN=40000';
$result = $connection->query( $query );

Boyke Ferdinandes · Accepted Answer · 2020-07-30T03:30:30.160

0

isnt it just a normal join..? hope I understand your question properly:

SELECT program_outcome, 
accreditation_standard
FROM program_outcome p
JOIN program_outcome_accreditation_standard_lookup l ON p.program_outcome_pk = l.program_outcome_fk
JOIN accreditation_standard a ON l.accreditation_standard_fk = a.accreditation_standard_pk;

check the result in fiddle below. or this query below. basically same result just closer to your expected result

SELECT program_outcome, 
GROUP_CONCAT(accreditation_standard SEPARATOR "\r\n") AS accreditation_standard
FROM program_outcome p
JOIN program_outcome_accreditation_standard_lookup l ON p.program_outcome_pk = l.program_outcome_fk
JOIN accreditation_standard a ON l.accreditation_standard_fk = a.accreditation_standard_pk
GROUP BY program_outcome;

fiddle

by the way, Im impressed with your field naming choice..

edited Jul 30 '20 at 03:30

answered Jul 30 '20 at 03:11

Boyke Ferdinandes

415
2
7

Both answers work, and with some slight changes formatting is what is required. I have updated my OP to show the final working solution with the formatting required. If I could, I would give both solutions as correct, but have chosen the simplest one... – Jul 30 '20 at 03:21
maybe you want to explain why did you use comma-join and left-join as @tcadidot0 asked in comment above? – Boyke Ferdinandes Jul 30 '20 at 03:28
Sorry, missed that...mixed methods? Otherwise no reason. – Jul 30 '20 at 03:30
1

@Tenderfoot The 'OP' is you. – philipxy Jul 30 '20 at 04:10

Group results from two tables

3 Answers3