Sql query with 3 related tables, get last purchase record for each product

Question

5.00/5 (1 vote)

See more:

I have 3 SQL tables in one database:

T1: Users, with columns: ID | Username | FirstName | LastName
ID column is Primary Key
T2: Purchases, with column: IdBid | ProductCode | PurchaserID | BidPrice | BidDate
IdBid column is Primary Key
ProductCode is Foreign Key to Table 3 to column IdProduct
PurchaserID is Foreign Key to Table 1 to column ID
T3: Products, with column: IdProduct | ProductName | ProductDescription | OpeningPrice
IdProduct column is Primary Key

Tables T1 and T3 don't have any relation.

I have auction and in T2 Purchases I can have many offers for one product and each offer is new record in T2 Purchases. In C# I have to create CollectionList (inheriting ObservableCollection) which should have list of last record for each product and it should have some column from all three above mentioned tables, as follows:

T3 ProductCode | T3 ProductName | T3 ProductDescription | T3 OpeningPrice | T2 PurchaserID | T1 PurchaserName | T2 BidPrice |

How should SQL query look like to get this?

Thank you in advance

What I have tried:

Many different SQL query options I can get the last record of each Product but only to show two columns, but I couldn't get all columns which I need.

Posted 23-Jun-19 0:04am

Sinisa Janjetovic

Updated 23-Jun-19 1:10am

Maciej Los

v2

Add a Solution

1 solution

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)

OriginalGriff · Accepted Answer · 2019-06-23T00:48:00

Solution 1

You need to use a JOIN, combined with a subquery using a GROUP BY.

The GROUP BY will aggregate records together by product, and you can select the ProductCode and the MAX(BidDate) for each record:

SQL

SELECT ProductCode, MAX(BidDate) AS LastBidDate 
FROM Purchases 
GROUP BY ProductCode

You can then use that to retrieve the rest of the bid row:

SQL

SELECT * FROM Purchases a
JOIN (SELECT ProductCode, MAX(BidDate) AS LastBidDate 
      FROM Purchases 
      GROUP BY ProductCode ) g
ON a.ProductCode = g.ProductCode AND a.BidDate = g.LastBidDate

Then it's a trivial JOIN to add in the other info you need.

Posted 23-Jun-19 0:48am

OriginalGriff

Comments

Sinisa Janjetovic 23-Jun-19 7:09am

Thank you, I'll test it

OriginalGriff 23-Jun-19 7:20am

You're welcome!

Maciej Los 23-Jun-19 7:11am

5ed!

Herman<T>.Instance 24-Jun-19 6:52am

Ojjj Griff! Use ROW_NUMBER function for such queries. It is designed for such db questions.

SELECT ProductCode, BidPrice, BidDate
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY ProductCode, BidDate ORDER BY ProductCode, BidDate DESC) as Rowno, ProductCode, BidPrice, BidDate
FROM Purchases
) as x
WHERE Rowno = 1

OriginalGriff 24-Jun-19 7:11am

That'll work as well.
I just figured that for a beginner a GROUP BY and JOIN was more obvious as to what was going on.

Herman<T>.Instance 24-Jun-19 7:51am

Both add to the learning curve

OriginalGriff 24-Jun-19 9:43am

Hah! This is SQL: it has a "Learning Brick Wall" rather than a curve, much like MSDOS manuals used to.

Have you ever looked at the docs for PIVOT and UNPIVOT? :laugh:

Herman<T>.Instance 24-Jun-19 19:03pm

have you ever read my article about dynamic pivotting?

OriginalGriff 25-Jun-19 1:34am

I didn't even know it existed!

And I'm halfway through my first coffee of the day (it's 06:30 here) so I'm not going to read it right now, my brain will run away and hide for the day if I try ... :laugh: