CODEWITHSUNDEEP
sqljoin
Dmitry Malchikov
Dmitry Malchikov

Reputation: 13

One table conditional join

Is it possible in SQL to pick data from 2 tables by join with condition and when second table not satisfies the conditions then pick null values from it?

I've done it like this: 

select * from Achievements left join (select * from PlayerAchievements where userId = 2) as t1 on t1.achievementsId = Achievements.Id

But I want to know is it possible to achieve the same result without sub-query?

Upvotes: 1

Views: 30

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution using a LEFT JOIN. The additional condition (userId = 2) is used on the mapping condition (ON) instead of WHERE. So you show all rows of the left table (Achievements) and only append the information of the right tables (PlayerAchievements) if the IDs of the achievements and the userId is matching. 

SELECT *  FROM Achievements A 
    LEFT JOIN PlayerAchievements PA ON A.Id = PA.achievementsId AND PA.userId = 2

Upvotes: 1

Related Questions