Click here to Skip to main content
12,625,447 members (35,964 online)
Rate this:
 
Please Sign up or sign in to vote.
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.6K
Updated 29-Sep-12 3:55am
v3
Comments
amolpatil2243 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 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 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161128.1 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2016
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