CODEWITHSUNDEEP
phpmysql
Mr.SH
Mr.SH

Reputation: 457

mysql get last 2 records of table with 3 tables

I have 3 mysql tables (order , camp , user) as bellow values,

order_table
ID    camp_id       orderDate       message
1       1           2015-01-01      ok
2       1           2015-02-01      ok
3       2           2015-03-01      not ok
4       3           2015-01-01      not ok
5       1           2015-04-01      not ok
6       2           2015-01-01      ok
7       3           2015-07-01      not ok

camp_table
camp_id camp_uid     camp name
1       10             first camp
2       11             second camp
3       12             third camp
4       10             forth camp

user_table
uid    uname
10      abc
11      xyz
12      wrt

i want to have result as bellow

uname,camp name,message

for last 2 records of each user from order_table for today order by orderDate

I want to join these tables to have uname from user_table and camp name from camp_table and message from order_table. for today order by orderDate Thanks

Upvotes: 5

Views: 217

Answers (3)

Basant Rules
Basant Rules

Reputation: 807

Order By Date and join both table.

  Select t1.camp_name, t2.uname,t3.message FROM order_table as t3
    LEFT JOIN camp_table as t1 on t3.camp_id = t1.camp_id
    LEFT JOIN user_table as t2 on t1.camp_uid = t2.uid
    order by t3.orderDate desc
    limit 2

Upvotes: 1

user5189304
user5189304

Reputation: 

SELECT
    u.uname,
    ct.camp_name,
    ot.message
FROM
    (
        SELECT
            *
        FROM
            order_table o1
        WHERE
            (
                SELECT
                    COUNT(*)
                FROM
                    order_table o2
                WHERE
                    o1.camp_id = o2.camp_id
                AND o2.ID >= o1.ID
            ) <= 2
    ) ot
INNER JOIN camp_table ct ON ct.camp_id = ot.camp_id
INNER JOIN user_table u ON ct.camp_uid = u.uid
ORDER BY
    u.uname

Upvotes: 2

Punit Gajjar
Punit Gajjar

Reputation: 4997

Select
ct.camp_name,
ut.uname,
ot.message FROM order_table as ot
LEFT JOIN camp_table as ct on ot.camp_id = ct.camp_id
LEFT JOIN user_table as ut on ct.camp_uid = ut.uid
order by ot.id desc
limit 2

Upvotes: 3

Related Questions