1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need a way to join the table using sql query

Discussion in 'SQL PL/SQL' started by laxman, Jul 19, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i have one requirement to get the output from an existing query including the records stored in one Log table.The information about all the table and the way i need output is being explained in detail below.

    Code (SQL):
    TABLE details:
    ---------------
    sql> SELECT * FROM req;

    reqid    mtid   stid   reqtypecode
    -----    ----   ----   -----------
    1111    17969  50482       1
    2222    17969  50480       1
    3333    17969  50481       1
    4444    17969  50483       1

    sql> SELECT * FROM mtree;

    pmtid  levelfromparent
    -----  ---------------
    17969       0

    sql> SELECT * FROM topic;

    topicid  topicdesc
    -------  ---------
     31492     topic  

    SQL > SELECT * FROM subtopic;

    topicid  subtopicid subtopicdesc   vtypecode
    -------  ---------- ------------   ---------
     31492    50480      subtopic1        1
     31492    50481      subtopic2        1
     31492    50482      subtopic3        1
     31492    50483      subtopic4        1
     
     SQL > SELECT * FROM logreq;
     
     reqid  topicid  subtopicid
     -----  -------  ----------
     1111    31492   50480
     1111    31492   50481
     1111    31492   50482

     
    current funtionality
    ----------------------
    when we execute this query we get the output as below

    Code (SQL):
    sql>SELECT Req.reqID,
      TOPICDESC,
      SUBTOPICDESC,
      FROM Req,
      Topic,
      Subtopic
      WHERE EXISTS
      (SELECT pmtid
      FROM mtree b
      WHERE b.pmtid = req.mtid
      AND b.pmtid  = 17969
      AND b.LEVELFROMPARENT   = 0
      )
    AND EXISTS
      (SELECT 1
      FROM Topic,
        SubTopic
      WHERE SubTopic.topicID       =Topic.topicID
      AND SubTopic.vTypeCode<2
      AND SubTopic.subtopicID      =req.stid
      AND LOWER(topicDesc) LIKE 'topic%'
      )
    AND Req.requestTypeCode      = 1
    AND Subtopic.subtopicID(+)       = Req.subtopicID
    AND Topic.topicID(+)             =Subtopic.topicID
    ORDER BY Req.reqID

    output
    -------
    reqid    topicdesc   subtopicdesc  
    -----    ----------       -----------    
    1111    topic          subtopic3              
    2222    topic          subtopic1              
    3333    topic          subtopic2            
    4444    topic          subtopic4  
    Proposed funtionality
    ------------------------
    I need to implement my existing query in such a way that it should include all records of table reqlog with the current output as mentioned above.

    Code (SQL):
    The output should be LIKE this
    ----------------------------------
    reqid    topicdesc   subtopicdesc  
    -----    ----------       -----------    
    1111    topic          subtopic1
    1111    topic          subtopic2
    1111    topic          subtopic3              
    2222    topic          subtopic1              
    3333    topic          subtopic2            
    4444    topic          subtopic4  
    Can anybody have any solution for this requirement.

    Regards
    Laxman
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am not pretty sure about your actual requirement. Anyway this is a wild guess.
    You can Union the out put from logreq along with existing query.

    You can removes the unwanted AND EXISTS clause if needed.


    Code (SQL):
    SELECT Req.reqID,
      TOPICDESC,
      SUBTOPICDESC
      FROM Req ,
      Topic,
      Subtopic
      WHERE EXISTS
      (SELECT pmtid
      FROM mtree b
      WHERE b.pmtid = req.mtid
      AND b.pmtid  = 17969
      AND b.LEVELFROMPARENT   = 0
      )
    AND Req.REQTYPECODE      = 1
    AND Subtopic.subtopicID(+)       = Req.STID
    AND Topic.topicID(+)             =Subtopic.topicID
    AND LOWER(topicDesc(+))  LIKE 'topic%'
    AND SubTopic.vTypeCode(+)  <2
    UNION  
    SELECT Req.reqID,
      TOPICDESC,
      SUBTOPICDESC
      FROM logreq req ,
      Topic,
      Subtopic
    WHERE Subtopic.subtopicID(+)       = req.SUBTOPICID
    AND Topic.topicID(+)             =Subtopic.topicID
    AND LOWER(topicDesc(+)) LIKE 'topic%'
    AND SubTopic.vTypeCode(+)  <2