Inner join on a distinct set of parameters

I've tried a few of the similar SO questions, but I can't seem to figure it out.

On the first inner join, I only want to bring in DISTINCT function columns code and serial_id. So when I do my SUM selects, it calculates one per distinct. Ie there are multiple rows with the same func.code and func.serial_id. I only want 1 of them.

SELECT
    sl.imp_id,
    lat.version,
    SUM(IF(lat.status = 'P',1,0)) AS powered,
    SUM(IF(lat.status = 'F',1,0)) AS functional
FROM slots sl
INNER JOIN functions func ON sl.id = func.slot_id
INNER JOIN latest_status lat ON lat.code = func.code
                             AND lat.serial_id = func.serial_id
WHERE sl.id=55
GROUP BY sl.imp_id, lat.version

EDIT 2 - sample data explanation -------------------

slots - id, imp_id, name
functions - id, slot_id, code, serial_id
latest_status - id, code, serial_id, version, status

    **slots**

    id  imp_id      name
    1    5       'the name'
    2    5       'another name'
    3    5       'name!'
    4    5       'name!!'
    5    5       'name!!!'
    6    5       'testing'
    7    5       'hi'
    8    5       'test'


    **functions**

    id   slot_id     code     serial_id
    1       1       11HRK        10
    2       2       22RMJ        11
    3       3       26OLL        01
    4       4       22RMJ        00
    6       6       11HRK        10
    7       7       11HRK        10
    8       8       22RMJ        00

    **latest_status**

   id     code     serial_id    version    status
    开发者_如何学Python1     11HRK       10         1           F
    1     11HRK       10         2           P
    3     22RMJ       11         1           P
    4     22RMJ       11         2           F
    5     26OLL       01         1           F
    6     26OLL       01         2           P
    7     22RMJ       00         1           F
    8     22RMJ       00         2           F

After running the query, the result should look like this:

imp_id    version    powered    functional
   5         1          1           3
   5         2          2           2

The function table gets rolled up based on the code, serial_id. 1 row per code, serial_id. It then gets joined onto the latest_status table based on the serial_id and code, which is a one (functions) to many (latest_status) relationship, so two rows come out of this, one for each version.

How about using DISTINCT?

SELECT
    SUM(IF(lat.status = 'P',1,0)) AS powered,
    SUM(IF(lat.status = 'F',1,0)) AS functional
FROM slots sl
INNER JOIN (Select DISTINCT id1, code, serial_id from functions) f On sl.rid = f.id1
INNER JOIN latest_status lat ON lat.code = f.code
                             AND lat.serial_id = f.serial_id
WHERE sl.id=55
GROUP BY sl.imp_id, lat.version

If you want only the distinct code and serial_id, you need to group by those not the imp_id and version. And end up with something like

SELECT
    SUM(IF(lat.status = 'P',1,0)) AS powered,
    SUM(IF(lat.status = 'F',1,0)) AS functional
FROM slots sl
INNER JOIN functions func ON sl.rid = func.id1
INNER JOIN latest_status lat ON lat.code = func.code
                             AND lat.serial_id = func.serial_id
WHERE sl.id=55
GROUP BY func.code, func.serial_id

However, this could all be rubish, without more data as tgo what some of those other columns are, but they dont seem to be the ones you wanted to group by.