SQL: Select from many-many through, joined on conditions in through table

Question

Can't get my head around this...

I have 3 tables like this:

Computers
---------
Id
Name


ComputerLogins
--------------
Computer_Id
User_Id
NumberOfLogins


Users
-----
Id
Name

Computers "have and belong to many" Users "through" ComputerLogins.

Sample data:

Computers:                  Id     Name
                             1  "Alpha"
                             2   "Beta"
                             3  "Gamma"

Users:                      Id     Name
                             1    "Joe"
                             2   "Fred"

ComputerLogins:    Computer_Id  User_Id  NumberOfLogins
                             1        1               5
                             1        2              12
                             2        1              10
                             2        2               6
                             3        1               2
                             3        2               4

I'm trying to construct a view that will output one row for each record in Computers, and join a Users row through MAX(NumberOfLogins) in ComputerLogins.

Desired output:

Computer_Id    User_Id    NumberOfLogins
          1          2                12
          2          1                10
          3          2                 4

Can you suggest a view query that will produce the desired output? Thanks!

Answer 1

Use:

CREATE VIEW your_view AS 
 SELECT c.id AS computer_id,
        u.id AS user_id,
        COUNT(*) AS NumberOfLogins
   FROM COMPUTERS c
   JOIN COMPUTERLOGINS cl ON cl.computer_id = c.id
   JOIN USERS u ON u.id = cl.user_id
GROUP BY c.id, u.id

Answer 2

SELECT
    CL.*, U.* --change this as needed
FROM
    (
    SELECT 
       Computer_ID, MAX(NumberOfLogins) AS NumberOfLogins
    FROM
       ComputerLogins
    GROUP BY
       Computer_ID
    ) maxC
    JOIN
    ComputerLogins CL On maxC.Computer_ID = CL.Computer_ID AND maxC.NumberOfLogins = CL.NumberOfLogins
    JOIN
    Users U On CL.User_ID = U.ID

Wrap in a view etc