subqueries

[untwisted]

Hi,

I'm trying to write a front end for a perl script a client of mine wrote. He wants me to get a list of messages from his database and then print a list of them. The list must be ordered by date (which I can do) and then grouped by topic (not sure if this is the right word to use, but I can't think of any other). Basically he wants me to have the parent thread at the top and each reply under it. I am having problems doing this. Right now this is the statement i'm using.

$sql_query_byDate = "SELECT subjfield, fromfield, datefield FROM `$db_TABLE` WHERE 1=1 GROUP BY `in_repl_tofield` ORDER BY `datefield`";

Now, it's grouping my messages by reply to field, which is half of the way there, but I need it to group the parent field with it. The parent column is called msg_idfield.

Thanks! I hope I explained everything well enough.

 

[Bastien]

try:

$sql_query_byDate = "SELECT subjfield, fromfield, datefield FROM `$db_TABLE` WHERE 1=1
GROUP BY msg_idfield,`in_repl_tofield`
ORDER BY `datefield`";

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[r937]

[tt]select subjfield, fromfield, datefield
from `$db_TABLE`
order
by coalesce(in_repl_tofield,msg_idfield)
, datefield desc[/tt]

the trick is in the COALESCE()

if the in_repl_tofield is null, that means it is a parent post, so it sorts under its own msg_idfield

if the in_repl_tofield is not null, that means it is a reply, so it sorts under its parent

so parent posts are sorted by their ids, with all their replies sorted under them by date descending


and if i knew of a techie job in toronto, i'd snap it up myself!! (actually, there's a ton of them on workopolis.com, check it out)

rudy

http://r937.com/

 

[untwisted]

Much thanks guys! If you're looking for programming might I suggest

http://www.rentacoder.com

that is what I'm using this SQL for. I found a client on rentacoder who lives in my area, and he ended up giving my partner and I a lot of work outside of rentacoder. If nothing else, it's a good way to make a quick buck.


Thanks again!!

-Brian