How to get previous owner in sql table?

Question

I have a Transaction table (transId, sn, customerId, date) that lists item transactions between customers. Some item has sn (serial number) and travels from one customer to another. For some customer (12345678), I need to find out who was the last previous owner of customer's items.

Here's my query:

   SELECT c.*, 
          p.transId, 
          p.customerId
     FROM Transaction c 
LEFT JOIN Transaction p ON c.sn = p.sn
    WHERE p.transId = (SELECT MAX(t.transId) 
                         FROM Transaction t 
                        WHERE t.transId < c.transId 
                          AND t.sn = c.sn)
     AND c.customerId = 12345678
ORDER BY p.transId;

This query works fine except when item has no previous owner. Should return nulls for p.transId and p.customerId but insead it returns 0 rows. Database is Access.

UPDATE: I need to have BOTH current owner and previous owner in result (in one row). And, it should work for middle owners (like a log; if the customer is not current owner, but was a owner before).

UPDATE: For certain customer (that would be passed as a parameter; in our case customerId=12345678), I need to see the list of all items that he ever owned and the last previous owner of the items (from which customer he got the item).

Some more explanations:

• transId is a primary key and autonumber (identity) - previous owner will have smaller transId than the newer owner
• customerId in transaction is the buyer (new owner after that transaction)
• date does not contain time; only date (should not be used in comparisons or ordering since some item can change two owners in one day)

Here is a little example that will make things more clearer (date not shown):

        transaction table
        -----------------------
        |transId|sn|customerId|
        |      1| 1|  12345678|
        |      2| 2|  87654321|
        |      3| 2|  12345678|
        |      4| 2|  11223344|
        |      5| 2|  12345678|
        -----------------------

        for customerId=12345678 result should be

        result
        --------------------------------------------------
        |transId|sn|customerId|prevTransId|prevCustomerId|
        |      1| 1|  12345678|       NULL|          NULL|
        |      3| 2|  12345678|          2|      87654321|
        |      5| 2|  12345678|          4|      11223344|
        --------------------------------------------------

Answer 1

Move condition of p.transId = ... from WHERE clause into a JOIN condition (and you do not need c.sn = p.sn in any case):

Normal SQL:

   SELECT c.*, 
          p.transId, 
          p.customerId
     FROM Transaction c 
LEFT JOIN Transaction p 
       ON p.transId = (SELECT MAX(t.transId) 
                         FROM Transaction t 
                        WHERE t.transId < c.transId 
                          AND t.sn = c.sn)
    WHERE c.customerId = 12345678
ORDER BY p.transId;

MS Access SQL:

SELECT      c.*, 
            p.transId, 
            p.customerId
FROM        Transaction c 
LEFT JOIN ((SELECT  t.transId, MAX(z.transId) AS PrevTransId
            FROM    Transaction t 
            LEFT JOIN Transaction z on t.sn = z.sn
            WHERE   z.transId < t.transId 
            GROUP BY t.transId) x
LEFT JOIN   Transaction p ON p.TransId = x.PrevTransId
           ) ON  x.TransId = c.TransId
WHERE       c.customerId = 12345678
ORDER BY    p.transId;