CODEWITHSUNDEEP
mysqlsql
IleNea
IleNea

Reputation: 609

Query two keys from the same table with different relationships

I have a table that has 2 relations with two other different tables.

User
id_user

Users_Licenses   (one-to-many unidirectional)
user(coach)
license_id

License
id
user_id (user simple) (one-to-one user)

Now I have to return the username of the user (one-to-one) or null if there is no one but I am not sure how to do that, because it always returns me the username of the coach. What I've tried:

SELECT ul.license_id, u.username, cl.created_at
FROM user u
INNER JOIN users_licenses ul ON ul.user_id = u.id
INNER JOIN license l ON l.id = ul.license_id
WHERE ul.user_id = :coachId

Upvotes: 0

Views: 116

Answers (3)

aalthawadi
aalthawadi

Reputation: 31

SELECT ul.license_id, u.username, cl.created_at
FROM user u, users_licenses ul, license l
where ul.user_id = u.id 
and  l.id = ul.license_id
and ul.user_id = coachId;

try to change the select statement to that:

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460098

Try this LEFT OUTER JOIN back to USER

SELECT ul.license_id, userLicensee.username as licensee
FROM user u
INNER JOIN users_licenses ul ON ul.user_id = u.id_user
INNER JOIN license l ON l.id = ul.license_id
LEFT OUTER JOIN user userLicensee ON l.user_id = userLicensee.id_user
WHERE ul.coachid = :coachId

Note that i have also changed WHERE ul.user_id = :coachId to ul.coachid = :coachId(you have not mentioned the name of the foreign-key column for the coach-user).

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

use LEFT OUTER JOIN

SELECT ul.license_id, u.username, cl.created_at
FROM users_licenses ul
LEFT OUTER JOIN license l ON l.id = ul.license_id
LEFT OUTER JOIN users u ON ul.user_id = u.id
WHERE ul.user_id = :coachId

Upvotes: 0

Related Questions