Taking the 2nd row from a table in innerjoin query

Question

4.00/5 (1 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 1:46am

arunrv

Updated 29-Sep-12 2:55am

Maciej Los

v3

Add a Solution

Comments

amolpatil2243 28-Sep-12 8:46am

Please provide the details of fields to analyse and find out some solution on query.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2012-09-29T03:03:00

Solution 2

You might use something like this:

SQL

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

Posted 29-Sep-12 3:03am

Maciej Los

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 ......

damodara naidu betha · Answer 2 · 2012-10-10T20:41:00

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

SQL

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