Re: Intermediate SQL question (groupby)

You Da Man ! Mechanical Mann. That works. Thanks a million

For posterity, I did have to make a tiny change. The id on the right of the where clause is different.

      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.id
GROUP BY   TMESSAGE.message, TSIMILAR_MESSAGE_TO_MESSAGE.ID
   HAVING     (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));

• Original Message ----- From: "Mechanical Mann" <eric_at_REMOVE_SPAMspeco.com> Newsgroups: comp.databases.oracle Sent: Tuesday, February 19, 2002 10:35 AM Subject: 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 - 20:22:02 CET