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

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

    case when c.Chno = 123 then c.Chno else null end Chno, 
    case when c.Chno = 123 then c.Chdate else null end Chdate 
    GrfileB g 
    join Challan c on g.Grno = c.Grno
    g.Custid = 1 
order by 

why do you want to do that? seems strange

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.