-
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
[/code]
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?
-
Re: A Query Doubt
Kindly post your table structure with some sample data for more details on the topic.
-
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)
-
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
-
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.