CODEWITHSUNDEEP
mysqlleft-joinsql-order-bylimit
jimmy turner
jimmy turner

Reputation: 13

MYSQL Limiting left join query

I have a query which uses a few left joins to bring back data. The dilemma is that i want to LIMIT and also ORDER BY the results from one of the left joins and thus limiting the total numbers of rows brought back.

The table i am trying to order and limit is table4

My query:

SELECT SQL_CALC_FOUND_ROWS e.meta_1, a.requestid, b.clientid, c.job_description, d.forename
FROM quotations_request AS a 

 LEFT JOIN table1 AS b ON (a.assigned_to = b.userid) 
 LEFT JOIN table2 AS c ON (a.job_costing = c.jobid) 
 LEFT JOIN table3 AS d ON (a.clientid = d.clientid) 
 LEFT JOIN table4 AS e ON (e.quotationid = a.requestid)

 WHERE a.archived = '0' AND (e.meta_1 = LCASE('requested') )

What i have tried:

SELECT SQL_CALC_FOUND_ROWS statuss.meta_1, a.requestid, b.clientid, c.job_description, d.forename,
 FROM quotations_request AS a 

 LEFT JOIN table1 AS b ON (a.assigned_to = b.userid) 
 LEFT JOIN table2 AS c ON (a.job_costing = c.jobid) 
 LEFT JOIN table3 AS d ON (a.clientid = d.clientid) 
 LEFT JOIN (Select meta_1 from table4 where quotationid = a.requestid ORDER BY uploaded_date DESC LIMIT 0, 1) 
 as statuss ON (statuss.quotationid = a.requestid)

 WHERE a.archived = '0' AND (statuss.meta_1 = LCASE('requested') )

The result:

After trying the above I got an error saying a.requestid is unkown in the where clause ( for the last left join)

Upvotes: 1

Views: 79

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Try this:

SELECT SQL_CALC_FOUND_ROWS statuss.meta_1, a.requestid, b.clientid, c.job_description, d.forename
 FROM quotations_request AS a 

 LEFT JOIN table1 AS b ON (a.assigned_to = b.userid) 
 LEFT JOIN table2 AS c ON (a.job_costing = c.jobid) 
 LEFT JOIN table3 AS d ON (a.clientid = d.clientid) 
 LEFT JOIN (
    select *
    from (select
            t.*,
            @rn := if(quotationid = @qid, @rn + 1, 
                if(@qid := quotationid, 1, 1)
            ) rn
        from (
        select *
        from table4
        order by quotationid, uploaded_date desc) t cross join (
            select @rn := 0, @qid := -1
        ) t2
    ) t where rn = 1
) 
 as statuss ON (statuss.quotationid = a.requestid)

 WHERE a.archived = '0' AND (statuss.meta_1 = LCASE('requested') )

The user variables @rn and @qid are used to find the top 1 row from table4 with latest uploaded_date and then join it with the other tables

Upvotes: 1

Related Questions