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';
2
Answers
Have you tried aliasing?
The problem is with the join predicate:
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: