MySql result set of n*n

Question

I have the following tables:

1. tag
2. tag_names
3. workout_names
4. workout_tags
5. workouts

My goal is to retrieve the workouts and tags in a result set which would go like like this.

workout
workout_tags

workout
workout_tags

I can retrieve one which is not a problem:

DELIMITER $$

CREATE PROCEDURE get_workouts_menu(IN _language SMALLINT(255))
BEGIN

DECLARE workout_id INTEGER;

SELECT w.workout_id,ROUND((w.duration / 1000) / 60) AS duration,w.isCustom,w.purchased,wn.name FROM workouts AS w INNER JOIN workout_names AS wn ON w.workout_id=wn.workout_id WHERE wn.language=_language ORDER BY  w.isCustom DESC,wn.name ;

#workout_id = QUERY ABOVE... you get the point

#SELECT tag_id FROM workout_tags WHERE workout_id = workout_id;

#SELECT * FROM tag_names WHERE tag_id = nn;

END$$

You get the point ,my question is how to be able to retrieve multiple workout tables and multiple tag tables.I'm having issues of saving the id also when there are more.As you can see in my code above, on i can get but how could i get one by one, or if someone has a better approach.Thanks...

Answer 1

You can't get two different types of results in a single SQL result set -- you can't have rows of workout information intermixed with tag information.

There are two ways to do it. You can join the tables, with separate rows for each tag -- they will have duplicate workout information. The calling application can display the workout information for the first row in the sequence, and skip it for subsequent rows. See How can i list has same id data with while loop in PHP? for how to do this in PHP.

Or you can use GROUP_CONCAT to get all the tag names in a comma-separated list in the same row as the workout information. The calling application can then split this list to display them on separate rows in a nested loop.

SELECT w.workout_id,ROUND((w.duration / 1000) / 60) AS duration,w.isCustom,w.purchased,wn.name, 
        GROUP_CONCAT(tn.name) AS tags
FROM workouts AS w 
INNER JOIN workout_names AS wn ON w.workout_id=wn.workout_id
LEFT JOIN workout_tags AS wt ON wt.workout_id = w.workout_id
LEFT JOIN tag_names AS tn ON tn.nn = wt.tag_id
WHERE wn.language=_language 
GROUP BY w.workout_id
ORDER BY  w.isCustom DESC, wn.name ;