how to select previous record with LEFT JOINed tables in MySQL?

Question

I'm struggleing to get the following to work:

# prev
SELECT klha.buyerID < PARAMETER_1

    FROM agents AS v

    LEFT JOIN seller_authorized AS klhs 
        ON klhs.agent= v.agentID 
        AND klhs.iln = v.seller
        AND v.`status` = 'auth'

    LEFT JOIN cust_list AS klha 
        ON klha.buyerID  = klhs.userID
        AND klha.accountNo = klhs.accountNo
        WHERE v.iln = PARAMETER_2
        AND klhs.acccountNo IS NOT NULL

    ORDER BY klha.buyerID 
    LIMIT 1 
;

END

The left joins are working as I'm using them in another procedure to select ALL relevent records. The problem is with my attempt to select the previous record.

I'm passing in a buyerID and need to get back the id of the previous record.

Question:
Can anyone point me to the correct syntax?

EDIT:
If I run this in MySQL, I'm getting "0" as resultset

Thanks!

SOLUTION:
Got it to work like this:

# prev
SELECT klha.buyerID

    FROM agents AS v

    LEFT JOIN seller_authorized AS klhs 
        ON klhs.agent= v.agentID 
        AND klhs.iln = v.seller
        AND v.`status` = 'auth'

    LEFT JOIN cust_list AS klha 
        ON klha.buyerID  = klhs.userID
        AND klha.accountNo = klhs.accountNo
        WHERE v.iln = PARAMETER_2
        AND klhs.acccountNo IS NOT NULL
        AND klha.buyerID < PARAMETER_1

    ORDER BY klha.buyerID 
    LIMIT 1 
;

Easier than thought :-) Thanks @Henrique Ordine

Answer 1

If I understand correctly what you need, adding this condition to your where clause should do the trick:

and klha.buyerID = (select max(buyerID) from agents where buyerID < PARAMETER_1)

Like this:

SELECT klha.buyerID 

    FROM agents AS v

    LEFT JOIN seller_authorized AS klhs 
        ON klhs.agent= v.agentID 
        AND klhs.iln = v.seller
        AND v.`status` = 'auth'

    LEFT JOIN cust_list AS klha 
        ON klha.buyerID  = klhs.userID
        AND klha.accountNo = klhs.accountNo
    WHERE v.iln = PARAMETER_2
      AND klhs.acccountNo IS NOT NULL
      and klha.buyerID = (select max(buyerID) from agents 
                            where buyerID < PARAMETER_1)

    ORDER BY klha.buyerID 
    LIMIT 1