Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Exclamation Unanswered: combining two tables of different datatypes

    I need to combine these two tables to get a single table that has the following column headings, thread, author,number of replies. Union does not work as the datatypes are different. I'm using oracle 8i. Any help appreciated .

    SQL> SELECT messageID "Thread",author
    2 FROM messages
    3 WHERE thread = 0;

    Thread AUTHOR
    ---------- --------------------
    44 colin
    141 noel
    142 noel
    143 noel
    159 colin
    160 colin
    163 colin

    7 rows selected.

    SQL> select thread,count(thread) "Replies"
    2 FROM messages
    3 WHERE thread !=0
    4 GROUP BY thread
    5 ;

    THREAD Replies
    ---------- ----------
    143 3
    146 1
    154 1
    155 1
    156 1
    160 1
    161 1

    7 rows selected.

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    You do not give us much info to go on.

    Assuming you have two different data bases because you have the same table name for both queries. Is that correct?

    I think you should create a view where your select joins the 2 tables. That way the view will stay current as you add to the source tables.

    Just an assumption though because I have no idea of what you big picture is.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Jan 2005
    Posts
    4

    Exclamation

    ya thats correct,
    this is the table

    SQL> desc messages;
    Name Null? Type
    ----------------------------------------- -------- -------------------
    MESSAGEID NOT NULL NUMBER
    TITLE NOT NULL VARCHAR2(50)
    AUTHOR VARCHAR2(20)
    BODY NOT NULL VARCHAR2(4000)
    BOARD NUMBER
    THREAD NOT NULL NUMBER
    DATE_CREATED NOT NULL DATE

    this is the query i got from another forum but its oracle 9i

    SELECT m.messageID, m.author, x.nReplies
    FROM messages m
    JOIN (
    SELECT thread,
    ( COUNT(*) + 1 ) AS "nMessages",
    COUNT(*) AS "nReplies"
    FROM messages
    WHERE thread <> 0
    GROUP BY thread
    ) x ON x.thread = m.messageID
    WHERE m.thread = 0


    i'm trying to combine two bits of info, selecting all the threads that have a value of 0, and selecting the remaining threads that dont equal zero. the threads that dont have a value of 0 are the replies to my messages...

    my problem is that out of the four columns that i want to combine from the same table (two views of the same table using alias names) three are numeric and one is text (author name so i cant use the union expression)...

    its a question of logic i think , and i just cant seem to see it...

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I think you are right on the logic part. Because I do not understand what you are trying to do.

    Uh, let me get this straight. It sounds like you are trying to recreate a table that already exists, but group the data differently?
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Hang on. I think my brain just started to understand.

    From my understanding I still think you want a view.

    Set up a database link to the other database.

    Create a view and the from clause should contain something like

    FROM messages local, databaselink.messages remote
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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