Re: Intermediate SQL question

Yet I'd prefer the following because you don't need to do the "group by" on the message text. This could be a big deal if the message text is long and numerous.

SELECT A.MESSAGE,
   A.MESSAGE_ID,
   B.CNT AS CountOfMESSAGE_ID
FROM
   TMESSAGE A,
   (SELECT C.ID,
      Count(*) AS CNT
    FROM TSIMILAR_MESSAGE_TO_MESSAGE C
    GROUP BY C.ID) B
WHERE B.CNT > 1; JP

David Bechtel <davidbechtel_at_attbi.com> wrote in message news:<3C77A3D2.CFEFF6BB_at_attbi.com>...
> You are almost there. I prefer the following syntax though.
>
> SELECT TMESSAGE.MESSAGE,
> TSIMILAR_MESSAGE_TO_MESSAGE.ID,
> Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID) AS CountOfMESSAGE_ID
> FROM SIMILAR_MESSAGE_TO_MESSAGE,
> TMESSAGE
> WHERE TMESSAGE.ID = TSIMILAR_MESSAGE_TO_MESSAGE.ID
> GROUP BY TMESSAGE, TSIMILAR_MESSAGE_TO_MESSAGE.ID
> HAVING (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));
>
> This should return just what you wanted.
>
> Dave Bechtel
>
> dsaf wrote:
>
> > 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 Mon Feb 25 2002 - 22:42:09 CET