How to do a sub-query for the same table i am selecting
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    Mar 2001
    Posts
    19
    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

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Thats an ODBC driver problem. If you run the same query using SQL+, you probably will not encounter any errors.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  4. #4
    Join Date
    Mar 2001
    Posts
    19
    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

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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]

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    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

    Sonali

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  8. #8
    Join Date
    Mar 2001
    Posts
    19
    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

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  10. #10
    Join Date
    Mar 2001
    Posts
    19
    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 ";


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width