Horizontal display of join 2 tables

[beary]

Hi,

I have 2 tables as follows:

table1 (studid,studfir st,studlast)
table2 (resid,studid,r esnum,resgrade)

In table 1 are 2 records:
Record 1: '1','Michael',' Jackson'
Record 2: '2','John','Wil liams'

In table 2 are 8 records:
1,1,1,A-
2,1,2,B+
3,1,3,C-
4,1,4,A
5,2,1,C
6,2,2,C
7,2,3,B-
8,2,4,B+

My sql is
SELECT table1.studfirs t, table1.studlast , table2.resnum, table2.resgrade
FROM table1
JOIN table2 ON table1.studid = table2.studid
ORDER BY table1.studlast , table2.resnum

The output is currently

[HTML]<table>
<tr><td>Michael </td><td>Jackson</td><td>1</td><td>A-</td></tr>
<tr><td>Michael </td><td>Jackson</td><td>2</td><td>B+</td></tr>
<tr><td>Michael </td><td>Jackson</td><td>3</td><td>C-</td></tr>
<tr><td>Michael </td><td>Jackson</td><td>4</td><td>A-</td></tr>
<tr><td<John</td><td>Williams </td><td>1</td><td>C</td></tr>
<tr><td<John</td><td>Williams </td><td>2</td><td>C</td></tr>
<tr><td<John</td><td>Williams </td><td>3</td><td>B-</td></tr>
<tr><td<John</td><td>Williams </td><td>4</td><td>B+</td></tr>
</table>
[/HTML]
How can I change my sql statement to display the results as follows (with each student's information shown in only 1 row), using only 1 query:

[HTML]<table>
<tr><th>First </th><th>Last</th><th>Res1</th><th>Res2</th><th>Res3</th><th>Res4</th></tr>
<tr><td>Michael </td><td>Jackson</td><td>A-</td><td>B+</td><td>C-</td><td>A-</td></tr>
<tr><td>John</td><td>Williams </td><td>C</td><td>C</td><td>B-</td><td>B+</td></tr>
</table>[/HTML]
I can do it by looping through a query for each student but I would like it all done in 1 statement. Surely this is fairly easy for mysql?

[beary]

Someone, please?

Is this problem too easy? Too hard? Can anyone help?

[coolsti]

I imagine you did not get an answer to your question yet, because what you are asking to do is not so normally done, and is not all that straightforward .

Usually, one would get all the data from the database in the query you use now, and then put it in whatever display form is desired using something like PHP.

Here it seems you are assuming there are only 4 result rows per student. Is this always the case? What would you do if there were only 2 or 3? Or if there were more?

The problem is, you want to create a return table that splits up one column (the results) to four colums (result 1 for student X, result 2 for student X, result 3 for student X, result 4 for student X) and this is maybe a difficult query to do and carry out "in one query".

Here is perhaps an alternative for you:

SELECT table1.studfirst, table1.studlast, table2.resgrade,
GROUP_CONCAT(table2.resnum separator '</td><td>') as result
FROM table1
JOIN table2 ON table1.studid = table2.studid
GROUP BY studid
ORDER BY table1.studlast, table2.resnum

This will combine all the results for each student (using the group by) and catenate all their results into one string, which I call here "result". Here, as an example, I made the string '</td><td>' to be the separator between the results. You could use anything you like. Using the above, you could add this "result" string directly to an html output if that is what you are doing.

[beary]

Coolsti,

Thankyou so much for replying! By the way, you are a legend. I slightly modified your code and it now does precisely what I needed. I appreciate your explanation of the issues as well. The code I have used is now

SELECT 
	table1.studfirst, 
	table1.studlast,  
	GROUP_CONCAT( table2.resgrade order by table2.resnum SEPARATOR '</td><td>' ) AS result
FROM table1
JOIN table2 ON table1.studid = table2.studid
GROUP BY table1.studlast
ORDER BY table1.studlast

I will play around with extending this to other tables and post back if I run into more trouble. Thanks again.

I imagine you did not get an answer to your question yet......
Here is perhaps an alternative for you:

SELECT table1.studfirst, table1.studlast, table2.resgrade,
GROUP_CONCAT(table2.resnum separator '</td><td>') as result
FROM table1
JOIN table2 ON table1.studid = table2.studid
GROUP BY studid
ORDER BY table1.studlast, table2.resnum

This will combine all the results for each student (using the group by) and catenate all their results into one string, which I call here "result". Here, as an example, I made the string '</td><td>' to be the separator between the results. You could use anything you like. Using the above, you could add this "result" string directly to an html output if that is what you are doing.

[coolsti]

I am a legend???

LOL

I am not even a programmer!

But seriously, I have used CONCAT and GROUP_CONCAT often to let a database do a lot of work for me. You can catenate html tags to your database values, or use html tags as separators to get your select query results in a format that you can use directly in your page.