Hi!
I am trying to write a procedure which extracts data from 2 tables named partymst and challan.

Fields in Grfile
1) Grno
2) Name

Fields in Challan
1)Grno
2)Chno
3)Chdate


Data in GrfileB]

CustID Grno Name
1 123 a
1 345 b
1 456 c
2 123 d
3 123 e
4 123 f


Data in Challan

Grno Chno Chdate
123 0001 01/01/2000
345 0002 01/01/2000
456 0003 01/01/2000
789 0004 01/01/2000
173 0005 01/01/2000
333 0006 01/01/2000

I want the records for custid=1

I have created a join on grno fields from both the tables.

Now, for the records which have Grno=123 in Grfile and Challan,one record will be fetched.

But in case,of the rest 2 records in Grfile which also have Grno=123, I want the fields from the Challan table to remain blank.

How do I write the procedure.

Th o/p I want is

Custid Grno Name Chno Chdate
1 123 a 0001 01/01/2000
1 345 b NULL NULL
1 456 c NULL NULL

select 
    g.Custid, 
    g.Grno, 
    g.Name, 
    case when c.Chno = 123 then c.Chno else null end Chno, 
    case when c.Chno = 123 then c.Chdate else null end Chdate 
from 
    GrfileB g 
    join Challan c on g.Grno = c.Grno
where 
    g.Custid = 1 
order by 
    g.Grno
This article has been dead for over six months. Start a new discussion instead.