VB.NET

VB.NET RSS

Many-to-one relationships

Please support our VB.NET advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!

•

Join Date: Sep 2007

Posts: 28

Reputation: nice_true is an unknown quantity at this point

Solved Threads: 0

nice_true

Offline

Light Poster

Many-to-one relationships

Sep 5th, 2007

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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Many-to-one relationships

Sep 5th, 2007

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
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
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