Using mysql, how can I relate many keys in one table to keys in another table using a single query?

Question

I have a mysql table named 'access' with 2 fields: 'owner_id' and 'date_created_id'. I have another table named 'usersx' with 2 fields: 'id', and 'last_name'. I would like to use a single query to get both last names from the 'usersx' table that match 'owner_id' and 'date_created_id' from the 'access' table.

For example if I have 'owner_id' and 'date_created_id' as 123 and 246 in the 'access' table, I would like to get the 'last_name' field from the 'usersx' table as 'Smith' and 'Jones' as those 'usersx.id's would match 'owner_id' and 'date_created_id'.

Here is what I tried:

SELECT usersx.last_name 
FROM usersx
JOIN access 
ON access.owner_id = usersx.id AND access.date_created_id = usersx.id
WHERE access.owner_id = '123' OR access.date_created_id = '246';

Answer 1

The problem is with the join predicate:

FROM userx us
JOIN access ac
    ON ac.owner_id = us.id AND ac.date_created_id = us.id

You can’t have a user row that matches both ids (unless they have the same value maybe). Instead, you would need to join twice:

SELECT o.last_name owner_last_name,
       d.last_name date_created_last_name
FROM access a
INNER JOIN usersx o ON o.id = a.owner_id
INNER JOIN usersx d ON d.id = a.date_created_id
WHERE a.owner_id = 123 OR a.date_created_id = 246