A few simple SQL problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rowy - 21 Apr 2005 14:36 GMT

Hello there,

I'm on my fourth day of learning SQL, and in the absence of good books
and tutorials, I'm resorting to online forums.

I've got a few questions regarding select statements. First of all -

I've got a table. I know that I can do: "Select Top 5 * from MyTable
orderby SomeOrder" to get the first five rows. What do I do to get the
next five rows?

I've got two tables - Blog and Comments. The Comments table has a
foreign key linking to the primary key of the Blog table. How do I list
the entries of my Blog table, together with a count of the number of
comments each entry has?

Thanks guys.
Rowy

Reply to this Message

Ed Prochak - 21 Apr 2005 19:09 GMT

q1:
TOP is not standard SQL.

What DBMS are you using? (my mindreading skills have left me) You'd do
best to RTFM for your DBMS.

BTW The assumption here is we are talking about Relational Databases.
One implication of this is THERE IS NO ORDER TO ROWS. Corect process is
to open a cursor and fetch the rows as they are returned.

q2:

it's a simple JOIN -

SELECT blogkey,COUNT(*) FROM blog, blogcomments
WHERE blog.blogkey=blogcomments.foreignkey
GROUP BY blogkey ;

HTH,
Ed

Reply to this Message

Rowy - 21 Apr 2005 20:22 GMT

Dear Ed,

Thanks a lot for the help. The answer you gave me for the blog was
nearly it. When I ran the statement, I was given the list of all the
blog entries which had comments in them, together with a count of the
number of comments each of them had.

Select
RowyBlog.EntryId,
count(*)
from
RowyComments,
RowyBlog
where
RowyBlog.EntryId = RowyComments.EntryId
Group by
RowyBlog.EntryId

However, what I really wanted was all the blog entries, with a count of
the number of comments in each of them (and not a list of only the blog
entries which had comments in them). I tried modifying it a bit and
instead put in

Select
RowyBlog.EntryId,
count(*)
from
RowyComments,
RowyBlog
where
RowyBlog.EntryId = RowyComments.EntryId
or
RowyBlog.EntryId > 0
Group by
RowyBlog.EntryId

Which instead returned a column with all the EntryId in my blog and
another column filled with the count of the number of comments. I'm
starting to doubt that this is the kind of thing that I can execute
with only one select statement. Which brings me back to the first
question - I'm using Microsoft's SQL Server 2005 XM Preview 1. Is there
a way to work with the results that you get back? Like, iterating
through them and running select statements on them? In any case, Ed,
thanks a lot for your answer. I sure can adapt it to make it work,
although I'll have to make two seperate calls to the database. That's
fine by me.

I also have a few other questions -

How can I alter my blog table so that if a blog entry is deleted, all
the comments associated with it are deleted as well? I'm fairly sure
this has something to do with the "Cascade" keyword.

Also, I am quite certain that using:

Select
*
From
<TableName>

isn't good at all - because of the huge amount of data that could be
returned. What's the normal alternative to this?

Thanks a lot for the help.
Rowy

Reply to this Message

Rolf Østvik - 22 Apr 2005 08:01 GMT

"Rowy" <Rowan.Rishi@gmail.com> wrote in news:1114111344.598755.3400
@f14g2000cwb.googlegroups.com:

> Dear Ed,
>
[quoted text clipped - 17 lines]
> the number of comments in each of them (and not a list of only the blog
> entries which had comments in them).

This works with PostgreSQL and should be standard

Select
RowyBlog.EntryId,
count(*)
from
RowyComments
left join RowyBlog
on RowyBlog.EntryId = RowyComments.EntryId
Group by
RowyBlog.EntryId

It might be that Microsoft's SQL Server use another join syntax but it
should be capable of the same functionality

Signature

Rolf

Reply to this Message

Rowy - 22 Apr 2005 15:47 GMT

Guys,

Thanks a lot for your help and support. I've tried Rolf's statement:

Select
RowyBlog.EntryId,
count(*)
from
RowyComments
left join RowyBlog
on RowyBlog.EntryId = RowyComments.EntryId
Group by
RowyBlog.EntryId

which actually returned me the same thing as the previous select
statement I tried (i.e. only with the entries which had comments in
them got listed). However, fooling around with the statement gave me
the results I wanted. I did:

Select
RowyBlog.EntryId,
count(RowyComments.EntryId)
from
RowyComments
right join RowyBlog
on RowyBlog.EntryId = RowyComments.EntryId
Group by
RowyBlog.EntryId

And got:
==================================
EntryID        (No Name)
0             0
10            2
20            1
30            0
40            0
70            0

Which was exacly what I wanted. Rolf, you gave me the right hints.
Thanks a lot everyone :)
Rowy
(Relieved)

Reply to this Message

Ed Prochak - 22 Apr 2005 17:26 GMT

Your are welcome, And thanks for the feedback (ie whether our advice
helped or not)

Now if we can just get you to stop thinking about using the database
like flat files, that would mean a great success. 8^)

Have a good day,
Ed

Reply to this Message

Bruce - 21 Apr 2005 19:54 GMT

Did you try this?

"Select Nexr 5 * from MyTable orderby SomeOrder"

> Hello there,
>
[quoted text clipped - 14 lines]
> Thanks guys.
> Rowy

Reply to this Message

Rowy - 21 Apr 2005 20:27 GMT

I just tried -

Select
Next 5 *
From
RowyBlog
Order By
EntryId

And was given the message -

.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '5'.

I don't think that next is defined as keyword in my DBMS - unlike the
"top" keyword, the "next" keyword doesn't get highlighted at all.

I really miss flat text files :'(
Sniff

Thanks Anyway Bruce :)
Rowy

Reply to this Message

Paul - 21 Apr 2005 22:20 GMT

>I really miss flat text files :'(
>Sniff

For all we know, that's what you're using. Tell us what RDBMS you are
using!

Paul...

>Rowy

Signature

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

Reply to this Message

Rowy - 22 Apr 2005 00:57 GMT

Hello there,

Right, so I'm using Microsoft's SQL Server Version 9.00.1090 - as for
the DDL... I really don't know where to find that. (I learnt about the
existence of DDL's like 15 mins ago)

I'm on XP Pro, SP1.

Hope that helps :S
Rowy

Reply to this Message

Lee Fesperman - 22 Apr 2005 02:30 GMT

> Hello there,
>
[quoted text clipped - 11 lines]
> the entries of my Blog table, together with a count of the number of
> comments each entry has?

Try the online SQL Tutorial at http://www.firstsql.com/tutor.htm. It only covers SQL92,
so there is nothing about 'Top', which isn't in SQL92.

Signature

Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

Evelyn V Stevens - 29 Apr 2005 21:07 GMT

>I've got a table. I know that I can do: "Select Top 5 * from MyTable
>orderby SomeOrder" to get the first five rows. What do I do to get the
>next five rows?

I don't have access to MS SQL Server, but in MS Access you can do this:

    SELECT Top 5 *
    FROM MyTable
    WHERE SomeColumn NOT IN
        (SELECT Top 5 SomeColumn
        FROM MyTable
        ORDER BY SomeColumn)
    ORDER BY SomeColumn;

Make sure you use the same "SomeColumn" everywhere to get the "next" 5, and
don't use more than one column in the subquery. As has been said, not
standard SQL at all.

Hope this helps,

                    Evelyn

Signature

Evelyn V. Stevens, Ph.D.          E-mail: evelyn@udel.edu
Information Technologies-User Services
U. of Delaware, Newark, DE 19716    Phone: 302-831-4240

Reply to this Message