Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Hello every one ,
 
I am working on a project where a customer has to book a property then the agreement will be there in that property.For that both payment is done through cash or Cheque so i am maintaining both booking payment and agreement payment in one table its self . and i have given Booking Id as my foreign Key . So while fetching agreement record i am getting both Booking payment details as well as agreement details. Here another thing i want to tell First Booking is inserted then only agreement will be inserted . There is no chance of inserting agreement first then booking so i am sure that the agreement is in second row .Now my problem is while fetching customer detail with payment using innerjoin i am getting both record. But i want 2 row of payment details with customer record. Same customer has done both booking and agreement in that i want Agreement part . So how can i write inner join or any alternate query to fetch my require data .
 

Example:
Consider I am having Booking table and payment table
in booking consider booking ID as Book123. Now this Booking ID is Forgien key in Payment table. Now customer books a property then a payment ID wll be generated
 
BookID is primary key
 
Customer Table
CustomerID   Name Address BookID
CS123        abc    xyz    Book123
 
Payment Table
PaymentID   BookID   Amount
1           Book123  10000
2           Book123  15800
 
Now when I inner join and fetch its coming like this
CS123        abc    xyz    Book123  1           Book123  10000
CS123        abc    xyz    Book123  2           Book123  15800
it's displaying both row, but i want only 2nd row
CS123        abc    xyz    Book123  2           Book123  15800
 
Thanks in Advance
Arun
Posted 28-Sep-12 2:46am
arunrv2.5K
Edited 29-Sep-12 3:55am
Maciej Los171.8K
v3
Comments
amolpatil2243 at 28-Sep-12 8:46am
   
Please provide the details of fields to analyse and find out some solution on query.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You might use something like this:
DECLARE @pid INT
 
SELECT @pid = MAX(PaymentID)
FROM Payment
WHERE P.BookID = 'Book123' 
 
SELECT P.*, C.*
FROM Payment AS P 
    LEFT JOIN Customer AS C ON P.BookID = C.BookID
WHERE P.PaymentID = @pid
  Permalink  
Comments
arunrv at 3-Oct-12 5:45am
   
If it was a single record to fetch means it will work but if i want to fetch whole record at a time means ???
Maciej Los at 3-Oct-12 16:12pm
   
???
You have wrote: it's displaying both row, but i want only 2nd row. So, what you realy want to do?
arunrv at 4-Oct-12 0:10am
   
ya only second row is displayed from both the row for a single booking.But if I consider the N-number of Customer records then how can I .I need to display it on GridView only the records of agreement I need to display . Thanks for replying ......
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I think, you need to display second record of each customer in a grid.If that so, you can achieve that using following sample code
 
  
 SELECT CUSTOMERID,BOOKID,NAME,ADDRESS,PAYMENTID,AMOUNT FROM
    ( 
     SELECT C.CUSTOMERID,C.BOOKID,C.NAME,C.ADDRESS,P.PAYMENTID,P.AMOUNT,
     ROW_NUMBER() OVER(PARTITION BY P.BOOKID,C.CUSTOMERID ORDER BY P.BOOKID) 
     AS ROWNUM FROM CUSTOMER C JOIN PAYMENT P 
     ON C.BOOKID = P.BOOKID
    ) 
  AS A WHERE A.ROWNUM = 2
 
 
 
Thank you
  Permalink  
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 434
1 OriginalGriff 380
2 CHill60 240
3 Abhinav S 220
4 CPallini 220
0 Sascha Lefévre 75
1 OriginalGriff 70
2 F-ES Sitecore 55
3 Peter Leow 50
4 Dave Kreskowiak 40


Advertise | Privacy | Mobile
Web04 | 2.8.150327.1 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100