MySQL Group by two columns from one table

[matthew stuart]

I am struggling with a MySQL select where I am trying to GROUP BY two different columns within a single table. Basically, from my example below you shouldd be able to see that my ID is my key column, and the ThreadID relates to the key column.

----------------------------------

| ID | ThreadID | Heading      |

----------------------------------

| 1  | 0        | First heading    |

| 2  | 0        | Second heading |

| 3  | 2        |                       | <--(No heading needed as it relates to ID 2)

| 4  | 2        |                       | <--(No heading needed as it relates to ID 2)

| 5  | 0        | Third heading  |

| 6  | 5        |                       | <--(No heading needed as it relates to ID 5)

| 7  | 5        |                       | <--(No heading needed as it relates to ID 5)

----------------------------------

I can get this working great by grouping by, but the problem is that I am getting the following results:

----------------------------------

| ID | ThreadID | Heading     |

----------------------------------

| 1  | 0        | First heading  |

| 4  | 2        |                      | <--(Heading of ID 2 disappeared)

| 7  | 5        |                      | <--(Heading of ID 5 disappeared)

----------------------------------

Basically, if a key column item doesn't have a related record, I am getting the heading printed as desired, if there is a related record, then the heading disappears. My question is, how can I retain the heading?

Here's my recordset select and variable:

SELECT *

FROM tbl_forumPOSTS JOIN tbl_users ON tbl_forumPOSTS.fld_fUSERID = tbl_users.UserID

WHERE fld_fSHOW = 1

GROUP BY fld_fTHREADID, colname

ORDER BY fld_fREPLYDATE DESC

Name: colname

Type: INT

Default: -1

Runtime: $_GET['fld_fID']

With the above recordset and variable, I am able to get the results within my DB table from 99 records down to 16, but I am losing my headings. It's because there isn't one in the record, so I need to tie it to the key record so I can pull it into place, but how?

Thanks in advance.

[bregent]

GROUP BY fld_fTHREADID, colname

First of all, what is the value of colname that you are passing in? In other words, what two columns are in your GROUP By clause?

[matthew stuart]

Ah yes, I edited my little table illustration to simplifiy my message, but forgot to do so in the recordset. In the recordset, runtime fld_fID should be just ID and fld_fTHREADID in the select should be ThreadID.

So, what I have done is exactly as you've mentioned, but I am missing the heading. I think this is because the query is returning a result for each item with the latest associated record (which has no heading) as the item to display.

So, I am now wondering if it's at all possible to have an ORDER BY ASC clause for each main record and it's associated records, and once those are ordered, then ORDER BY DESC.

SELECT *

FROM tbl_forumPOSTS JOIN tbl_users ON tbl_forumPOSTS.fld_fUSERID = tbl_users.UserID

WHERE fld_fSHOW = 1

(GROUP BY ThreadID, colname ORDER BY ID ASC) <-- so this one orders the grouped records based on their association with each other

ORDER BY fld_fREPLYDATE DESC <-- this one orders the list displayed

This is throwing an error though, so I'm not even sure if it's possible to have two ORDER BY in one SELECT even though I am trying to order a group, and then order all the groups returned... does that make sense?

[bregent]

OK, the first thing you should know is that your SQL is invalid. When using a GROUP BY clause, all columns that are not listed in that clause must be part of an aggregate function. In other words, the GROUP BY clause is used for aggregation. In any other DBMS, your SQL would have generated an error message. MySQL, instead of returning and error, returns garbage. I'm betting that you really don't want to do any aggregation, so grouping is not the solution for you.

Your table is self referencing - that is - a column from one row refers to a different column of another row. So you will probably need to create an alias table. To get a better understanding of what you want, please include more sample data and then a table of what you want the results to look like.

[matthew stuart]

Yeah, grouping probably wasn't the best thing to do. This is the trouble when you work in isolation... sometimes you just can't see the wood for the trees.

In the end I removed the group by and let the whole results to display basically display regardless of heading being present, I then inserted a recordset inside the repeating region along with a hand-made variable:

$fID = $row_tbl_forumPOSTS['fld_fTHREADID'];

SELECT * FROM tbl_forumPOSTS WHERE fld_fID = '".$fID."' ORDER BY fld_fID DESC

From this recordset, I then placed the heading next to the one that was already there from the previous recordset, and just to indicate if it was pulling the correct content through, I put two slashes between them... turns out that it's a better UX as it takes me directly to the record in question rather than to all associated... a happy accident which doesn't often happen to me!

Thanks anyway as your last advice lead me to try another route which worked.