Many-to-one relationships
 

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


[B][U]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

Re: Many-to-one relationships
 

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

Re: Many-to-one relationships
 

why do you want to do that? seems strange