Re: Intermediate SQL question

Try this

      SELECT   TMESSAGE.message, TSIMILAR_MESSAGE_TO_MESSAGE.ID,
                        Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID) AS
CountOfMESSAGE_ID
        FROM   TSIMILAR_MESSAGE_TO_MESSAGE, TMESSAGE
     WHERE   TMESSAGE.message_id  =  TSIMILAR_MESSAGE_TO_MESSAGE.message_id
GROUP BY   TMESSAGE.message, TSIMILAR_MESSAGE_TO_MESSAGE.ID
   HAVING     (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));"

"dsaf" <zxcv_at_gf.ll> wrote in message
news:p7jc8.66438$Cg5.3892987_at_news1.calgary.shaw.ca...
> I prefer leaving SQL to the gurus, but this is a small project, so I have
> the joy of writing the entire app.
>
>
> My problem
>
> I have two tables
>
> TMESSAGE
> with fields
> message_id, message
>
> and
> TSIMILAR_MESSAGE_TO_MESSAGE
> with fields
> id, message_id
>
> TMESSAGE.message_id relates to both TSIMILAR_MESSAGE_TO_MESSAGE.message_id
> and TSIMILAR_MESSAGE_TO_MESSAGE.id as a one to many relationship.
> TSIMILAR_MESSAGE_TO_MESSAGE.message_id and TSIMILAR_MESSAGE_TO_MESSAGE.id
> together make the primary key for their table.
>
> Now I want to group the similar messages to see which is the largest group
> of similar messages.
> This is easy:
>
> "SELECT TSIMILAR_MESSAGE_TO_MESSAGE.ID,
> Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID) AS CountOfMESSAGE_ID
> FROM TSIMILAR_MESSAGE_TO_MESSAGE
> GROUP BY TSIMILAR_MESSAGE_TO_MESSAGE.ID
> HAVING (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));"
>
> This provides a table as follows
>
> message_id CountOfMESSAGE_ID
>
> 24 7
> 31 4
> 36 3
>
> The problem is I also want the message returned in the query
> (TMESSAGE.message).
> In other words I would like a resultset that looks as follows:
>
> message message_id CountOfMESSAGE_ID
>
> I am a message 24 7
> I am a message too 31 4
> I am a message also 36 3
>
> I'm sure it's fairly simple, but I just can't quite figure it out. Any
 help
> would be most welcome.
>
>
>
>
Received on Tue Feb 19 2002 - 19:35:58 CET