Results 1 to 5 of 5

Thread: A Query Doubt

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    A Query Doubt

    From a table i have to display supplierryotnumber,receiverryotnumber, surityryotnumber1,surityryotnumber2 with their names. All their names are in a signle master table i.e. Ryotmaster i have written the following query
    Code:
    select a.supplieryotnumber,b.ryotname,a.recieverryotnumber,c.ryotname,a.surityryotnumber1, 
    d.ryotname, a.surirtyryotnumber2, e.ryotname from cmsplotmaster a, cmsryotmaster b, 
    cmsryotmaster c, cmsryotmaster d, cmsryotmaster e where a.supplierryotnumber = b.ryotnumber and
     a.recieverryotnumber = c.ryotnumber and a.surityryotnumber1 = d.ryotnumber and 
    a.surityryotnumber2 = e.ryotnumber
    it will display ryots details who are having guarantors(i.e. Surity1 and surity2) but i have to display all the ryots details irrespective of whether they have guarantors or not. If guarators are there i have to display their details. To use union we should have same number of columns. But if a ryot do not have surityryotnumbers then no of columns will be different.
    Can anybody suggest how to write a query without using procedure to solve this problem?

    Last edited by debasisdas; 10-26-2007 at 07:25 AM. Reason: Change Formatting

  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: A Query Doubt

    Kindly post your table structure with some sample data for more details on the topic.


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: A Query Doubt

    Plotmaster table structure is
    supplierryotnumber varchar2(9), receiverryotnumber varchar2(9),plotnumber varhcar2(10), pk surityryotnumber1 varchar2(9), surityryotnumber2 varchar2(9), divisioncode varchar2(2),
    sectioncode varchar2(2), villagecode varchar2(3), seasoncode varchar2(6)

    ryotmaster table structure is
    ryotnumber varchar2(9), pk
    ryotname varchar2(40), divisioncode varchar2(2), sectioncode varchar2(2), villagecode varchar2(3)

    Last edited by krishnaindia2007; 10-26-2007 at 07:52 AM. Reason: Clarity Purpose

  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: A Query Doubt

    Sample Data for Cmsplotmaster
    sup.ryotno rec ryotno plotno surity1 surity2 div sec vill seasoncode
    1. 01008B003 01008H001 R010080144 01008K002 01008L002 01 02 152 200607
    2. 01008R003 01008R007 R010591532 NULL NULL 02 02 233 200607

    Sample Data for Cmsryotmaster
    ryotnumber ryotname div sec vill
    1.01008B002 BALARAM.V 01 02 152
    2.01008R003 RAJENDRA NAIDU.M 02 02 233

    Last edited by krishnaindia2007; 10-26-2007 at 07:55 AM.

  5. #5
    Junior Member
    Join Date
    Mar 2007
    Answers
    1

    Re: A Query Doubt

    simple solution could be

    add a record with RYOTNUMBER=0 in the master and then use
    NVL function.

    OR

    use OUTER JOIN.

    If you are not OK with the solution, let me know.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact