Thread View: bit.listserv.sqlinfo
6 messages
6 total messages
Started by dsaf
Tue, 19 Feb 2002 02:54
Intermediate SQL question
Author: dsaf
Date: Tue, 19 Feb 2002 02:54
Date: Tue, 19 Feb 2002 02:54
52 lines
2886 bytes
2886 bytes
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.
Re: Intermediate SQL question
Author: brayasam
Date: Thu, 21 Feb 2002 04:40
Date: Thu, 21 Feb 2002 04:40
73 lines
3439 bytes
3439 bytes
Just some tricks :) try this. SELECT min(Message) as Msgm, 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));" Hope this helps. B. Rayasam. "dsaf" <zxcv@gf.ll> wrote in message news:p7jc8.66438$Cg5.3892987@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. > > > >
Re: Intermediate SQL question
Author: David Bechtel
Date: Sat, 23 Feb 2002 14:13
Date: Sat, 23 Feb 2002 14:13
145 lines
7667 bytes
7667 bytes
--------------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. I prefer the following syntax though.</tt><tt></tt>
<p><tt>SELECT TMESSAGE.MESSAGE,</tt>
<br><tt> TSIMILAR_MESSAGE_TO_MESSAGE.ID,</tt>
<br><tt> Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID)
AS CountOfMESSAGE_ID</tt>
<br><tt> FROM SIMILAR_MESSAGE_TO_MESSAGE,</tt>
<br><tt> TMESSAGE</tt>
<br><tt> WHERE TMESSAGE.ID = TSIMILAR_MESSAGE_TO_MESSAGE.ID</tt>
<br><tt> 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 CountOfMESSAGE_ID</tt><tt></tt>
<p><tt>24
7</tt>
<br><tt>31
4</tt>
<br><tt>36
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
message_id CountOfMESSAGE_ID</tt><tt></tt>
<p><tt>I am a message
24
7</tt>
<br><tt>I am a message too
31
4</tt>
<br><tt>I am a message also 36
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--
Re: Intermediate SQL question
Author: David Bechtel
Date: Sat, 23 Feb 2002 14:19
Date: Sat, 23 Feb 2002 14:19
95 lines
4238 bytes
4238 bytes
Sorry to stick my nose in here, but isn't MIN(Message) a function to return the minimum value in the Message column? Assuming that Message is a number. Since this isn't the case, this won't do what he wanted. However, If your thought was to return the messages in length order based on what dsaf showed us, then you would want to calculate the length of the string Message and order the results by that value. Also, to get this reference to Message to work, you need to add TMESSAGE to the From clause and add a Where clause linking the two tables. Hope this helps, Dave Bechtel brayasam wrote: > Just some tricks :) > > try this. > > SELECT min(Message) as Msgm, 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));" > > Hope this helps. > > B. Rayasam. > > "dsaf" <zxcv@gf.ll> wrote in message > news:p7jc8.66438$Cg5.3892987@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. > > > > > > > >
Re: Intermediate SQL question
Author: J.P.
Date: Mon, 25 Feb 2002 21:42
Date: Mon, 25 Feb 2002 21:42
89 lines
3922 bytes
3922 bytes
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@attbi.com> wrote in message news:<3C77A3D2.CFEFF6BB@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.
>
> --
SQL question
Author: Marian
Date: Thu, 28 Feb 2002 01:14
Date: Thu, 28 Feb 2002 01:14
17 lines
2079 bytes
2079 bytes
If I sent this to the wrong address, please forgive me. I lost all my
e-mail and settings and have been trying to get everything set back up.
Anyway,
I'm trying to get a date to print from the following select statement, that
is 14 days prior to the current date and have it print out in date format
Feb 27, 2002. Can anyone tell what I'm doing wrong??
&run_statement("select subdate('date_format(now('CURRENT_DATE'), '%b %e,
%Y') CURRENT_TIME', interval '-14 0:0' day_minute);");
while(($begin_date) = $sth->fetchrow){
print "$begin_date";
}
Thanks,
Marian
Thread Navigation
This is a paginated view of messages in the thread with full content displayed inline.
Messages are displayed in chronological order, with the original post highlighted in green.
Use pagination controls to navigate through all messages in large threads.
Back to All Threads