Thread: 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?

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)

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