🚀 go-pugleaf

--------------BD82BBC2A5C6E0284E3ADA7E
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.

--------------BD82BBC2A5C6E0284E3ADA7E
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<tt>You are almost there.&nbsp; I prefer the following syntax though.</tt><tt></tt>
<p><tt>SELECT&nbsp; TMESSAGE.MESSAGE,</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TSIMILAR_MESSAGE_TO_MESSAGE.ID,</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID)
AS CountOfMESSAGE_ID</tt>
<br><tt>&nbsp; FROM&nbsp; SIMILAR_MESSAGE_TO_MESSAGE,</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TMESSAGE</tt>
<br><tt>&nbsp;WHERE&nbsp; TMESSAGE.ID = TSIMILAR_MESSAGE_TO_MESSAGE.ID</tt>
<br><tt>&nbsp;GROUP BY TMESSAGE, TSIMILAR_MESSAGE_TO_MESSAGE.ID</tt>
<br><tt>HAVING (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));</tt><tt></tt>
<p><tt>This should return just what you wanted.</tt><tt></tt>
<p><tt>Dave Bechtel</tt><tt></tt>
<p><tt>dsaf wrote:</tt>
<blockquote TYPE=CITE><tt>I prefer leaving SQL to the gurus, but this is
a small project, so I have</tt>
<br><tt>the joy of writing the entire app.</tt><tt></tt>
<p><tt>My problem</tt><tt></tt>
<p><tt>I have two tables</tt><tt></tt>
<p><tt>TMESSAGE</tt>
<br><tt>with fields</tt>
<br><tt>message_id, message</tt><tt></tt>
<p><tt>and</tt>
<br><tt>TSIMILAR_MESSAGE_TO_MESSAGE</tt>
<br><tt>with fields</tt>
<br><tt>id, message_id</tt><tt></tt>
<p><tt>TMESSAGE.message_id relates to both TSIMILAR_MESSAGE_TO_MESSAGE.message_id</tt>
<br><tt>and TSIMILAR_MESSAGE_TO_MESSAGE.id as a one to many relationship.</tt>
<br><tt>TSIMILAR_MESSAGE_TO_MESSAGE.message_id and TSIMILAR_MESSAGE_TO_MESSAGE.id</tt>
<br><tt>together make the primary key for their table.</tt><tt></tt>
<p><tt>Now I want to group the similar messages to see which is the largest
group</tt>
<br><tt>of similar messages.</tt>
<br><tt>This is easy:</tt><tt></tt>
<p><tt>"SELECT TSIMILAR_MESSAGE_TO_MESSAGE.ID,</tt>
<br><tt>Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID) AS CountOfMESSAGE_ID</tt>
<br><tt>FROM TSIMILAR_MESSAGE_TO_MESSAGE</tt>
<br><tt>GROUP BY TSIMILAR_MESSAGE_TO_MESSAGE.ID</tt>
<br><tt>HAVING (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));"</tt><tt></tt>
<p><tt>This provides a table as follows</tt><tt></tt>
<p><tt>message_id&nbsp;&nbsp;&nbsp;&nbsp; CountOfMESSAGE_ID</tt><tt></tt>
<p><tt>24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
7</tt>
<br><tt>31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4</tt>
<br><tt>36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
3</tt><tt></tt>
<p><tt>The problem is I also want the message returned in the query</tt>
<br><tt>(TMESSAGE.message).</tt>
<br><tt>In other words I would like a resultset that looks as follows:</tt><tt></tt>
<p><tt>message&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
message_id&nbsp;&nbsp;&nbsp;&nbsp; CountOfMESSAGE_ID</tt><tt></tt>
<p><tt>I am a message&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
7</tt>
<br><tt>I am a message too&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4</tt>
<br><tt>I am a message also&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
3</tt><tt></tt>
<p><tt>I'm sure it's fairly simple, but I just can't quite figure it out.
Any help</tt>
<br><tt>would be most welcome.</tt></blockquote>
<tt></tt></html>

--------------BD82BBC2A5C6E0284E3ADA7E--