Many-to-one relationships
Please support our VB.NET advertiser: DiscountASP.NET – 3 Months Free on VB.NET Web Hosting
 |
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
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
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 why do you want to do that? seems strange
Last edited by matale : Sep 5th, 2007 at 12:43 pm. Reason: wa wrong
My current Project www.footystat.com
|
Similar Threads
Other Threads in the VB.NET Forum
- Word Association Game (Posting Games)
- Interests Anyone? (Geeks' Lounge)
- Many-to-Many Relationships in O/R Mapping (ASP.NET)
- MySQL - logical model from a conceptual model (MySQL)
- Importing SQL Script File - Urgent !! (Database Design)
- Subject: Software Developer earning his Doctorate needs help (Geeks' Lounge)
- Where can I buy a good computer? (Geeks' Lounge)
Other Threads in the VB.NET Forum
- Previous Thread: DataSets in Excel to be used in VB
- Next Thread: Threaded Backup Utility
•
•
•
•
Views: 931 | Replies: 2 | Currently Viewing: 1 (0 members and 1 guests)
Linear Mode
