How to do a sub-query for the same table i am selecting

[seenu27]

group,

I want to do a sub-query for the same table i am selecting.

here is my query:

select a.messageID, a.message, a.title, a.updatedTime, (select b.message, b.title from bulletin_messages b where b.prevID > -1 and b.prevID=a.messageID) from bulletin_messages a.

First i want to select all the messages and in the same query i want to select the replied for those messages. Is that possible??.

I am getting an error like this:
Problem in SQL [Microsoft][ODBC SQL Server Driver][SQL Server]Only one expression can be specified in the select list when the subquery is not introdu
ced with EXISTS.

Can anyone help me out please??

Thanks
seenu

[Halo]

Thats an ODBC driver problem. If you run the same query using SQL+, you probably will not encounter any errors.

[chrisrlong]

Meaning that you need to specify it as a pass-through query.

Also, "b.prevID > -1 and b.prevID=a.messageID" seems redundant, no?

- Chris

[seenu27]

First letme explain the table design

I have a table called messages in which i have the following columns

messageID prevID message title updatedTime.

Whenever the first message has been posted i will insert a record in this table with values as follows:

for example if the message is test and title is testtitle

messageID prevID message title updatedTime
1 -1 test testtitle 10:07:2001

Whenever there is an reply to this message i will insert another record as follows:

for example if the reply message is "reply" and the title is "replytitle"

messageID prevID message title updatedTime
1 -1 reply replytitle 10:07:2001

I dont want to maintain a seperate table called "ANSWER" for all the messages because all the replies are itself an message.

First i want to select all the messages from the table and if there is any "REPLIES" i want to select them as well so that i would align them in a seperate row. That is the reason i am passing a subquery with:

b.prevID > -1 and b.prevID = a.messageID.

Please letme know if i am anywhere unclear in my explanation and looking forward for your help,
Seenu

[chrisrlong]

Thanks for the explanation. I will get back to what I gleaned from it in a minute.

First, however, almost *regardless* of the usage, this statement is redundant:
b.prevID > -1 and b.prevID = a.messageID.

Assuming, of course, that a.messageID can never be less than 0.

Mind you, having redundant predicates is far better than not having enough, so it is a very minor point. I just want to make sure that you understand that it *is* redundant.

If a.MessageID can never be less than 0, and since you are joining it to b.prevID, it follows that none of the resulting records can possibly have a value of prevID that is less than 0, or more specifically, -1, correct?

Anyway, moving on. The first problem is that your example is incorrect. Well, let's backup further. Your idea of a single table is absolutely correct. You *have* to have a single table. Otherwise, you would have a definite problem because replys can be replys to messages *or other replies*. You are absolutely correct that a reply is simply another message.

However, I would rename your prevID field, as this is not terribly descriptive. I would call it something like either ParentID or ReplyToID. So, in your example where we have a reply to message 1, the correct values for this new entry would be:

messageID.ReplyToID.message.title........updatedTime
2...............1...............reply......replytitle.10:07:2001

Correct?

Now, you say:
"First i want to select all the messages from the table and if there is any "REPLIES" i want to select them as well so that i would align them in a seperate row. "

Which basically says that you want all the records, no? You only have 2 types, base messages and replies, right? And you want them both. So what is wrong with SELECT * FROM Bulletin_Messages?

If you want them to display in order, something like
SELECT * FROM Bulletin_Messages ORDER BY DECODE(ReplyToID,-1,MessageID), MessageID would work.

Let's take this a step further. Notice that what you have is a hierarchical structure. You can therefore do:
SELECT
---RPAD ( ' ', 3 * ( LEVEL - 1 ), ' ' )||
------Title || ': ' || Message
FROM
---Bulletin_Messages
START WITH
---ReplyToID---=----1
CONNECT BY
---PRIOR ID---=---ReplyToID;
to actually return them in a tree structure

However, after all that, I still can't figure out what you were trying to do with the sub-select.

Let me know if any of this makes sense,

- Chris

[Edited by chrisrlong on 05-04-2001 at 02:27 PM]

[sonaliak]

Hi Chris,
This query is just great but I don't understand what connect by does.. Is it like a join ?
I did not find any help on this.
If you are using sequences to add the key will you need start with, is it same as order by ???


thanks a lot

[chrisrlong]

It's used to to return a hierarchical result set.

Look at:
http://technet.oracle.com/docs/produ...7/state21b.htm

...for more info

The START WITH tells it where the tree starts, and the CONNECT BY basically tells it what field points to the parent.

(Parent column)=(pointer to parent column)

HTH,

- Chris

[seenu27]

Chris,

That was an BRIILLIANT EXPLANATION. Thanks for the time and effort spend for me chris, i am really realy thankful to you. But i cannot able to understand the following query, can you please shed some light in this one please.

SELECT
---RPAD ( ' ', 3 * ( LEVEL - 1 ), ' ' )||
------Title || ': ' || Message
FROM
---Bulletin_Messages

Looking forward for your help,
Seenu

[chrisrlong]

Basically, I'm telling it to indent 3 spaces for each level. This is how the standard EXPLAIN PLAN query is done, with each line indented the proper amount so things line up. So, your results would look like this: ('-' = ' ')

Original Message
---Reply 1 to message
---Reply 2 to message
------Reply 2.1 to Reply 2
---------Reply 2.1.1 to Reply 2.1
------Reply 2.2 to Reply 2
---Reply 3 to message

Make sense?

- Chris

[seenu27]

Chris,

Thanks for the explanation. However when i try to run this query it says syntax error near "Level", can you please help me out in this one.

Thanks
Seenu

Here is my query
-----------------------

String clientURLQuery = "SELECT "+
"RPAD ( ' ', 3 * ( LEVEL - 1 ), ' ' )|| "+
"Title || ': ' || Message "+
"FROM"+
"Bulletin_Messages "+
"START WITH "+
"ReplyToID=-1 "+
"CONNECT BY "+
"PRIOR ID=ReplyToID ";