CODEWITHSUNDEEP
mysqljoin
user12013126
user12013126

Reputation:

Group results from two tables

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>";

Upvotes: 1

Views: 65

Answers (3)

user12013126
user12013126

Reputation:

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 );

Upvotes: 1

Boyke Ferdinandes
Boyke Ferdinandes

Reputation: 411

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..

Upvotes: 0

Grocker
Grocker

Reputation: 1044

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

Upvotes: 1

Related Questions