CODEWITHSUNDEEP
mysql
Gowri
Gowri

Reputation: 16855

Mysql join query problem

My tables

product

pid name

1    AA
2    bb
3    cc

History table

hid pid uid 
1    1   1
2    1   2
3    1   1  // this one should join with pid 1
4    3   2  // this one should join with pid 3
5    2   3
6    2   1 // this one should join with pid 2

I like to display most recent bidder on a product.The history tables stores bidder details.if no bid on product just need to return null.

Thanks

Upvotes: 0

Views: 52

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166606

How about something like

SELECT  *
FROM    product p LEFT JOIN
        (
            SELECT  ht.*
            FROM    History_table ht INNER JOIN
                    (
                        SELECT  pid,
                                MAX(hid) last_hid
                        FROM    History_table ht
                        GROUP BY    pid
                    ) lstItem   ON  ht.pid = lstItem.pid
                                AND ht.hid = lstItem.last_hid
        ) ht    ON  p.pid = ht.pid

First you need to retrieve the MAX hid per pid, which by definition should be the most recent entry.

Then join that back to the same history table to retrieve the uid.

And lastly join this (LEFT JOIN) back to the actual products table.

Hope that helps.

Upvotes: 2

Related Questions