Distinct users while using join statement

Question

I have a huge database 'Job seeker' having multiple table like username, skills, qualification, job, social media, etc. One user can have multiple columns on job, social media, etc. table. Now if I use simple inner join I get multiple columns for single user.

Select user.user_id, user.name, s.skill_name, s.type , q.qualification, j.job_name, s_url
from username user
inner join skills s on s.skill_user = user.user_id
inner join qualification q on q.qualification_user = user.user_id
inner join job j on j.job_user = user.user_id

But what if I want top 1 row for each user. How can I do it?

Edit: Is there some other way to do this instead of using distinct?

Answer 1

A simple way is to use distinct on ()

Select distinct on (user.user_id) user.user_id, user.name, s.skill_name, s.type , q.qualification, j.job_name, s_url
from username "user"
  join skills s on s.skill_user = user.user_id
  join qualification q on q.qualification_user = user.user_id
  join job j on j.job_user = user.user_id
order by user.user_id;

This picks a "random" row from the result.

A more complicated way (but possibly more efficient) would be to pick only one row for each of the joined tables:

select distinct on (user.user_id) user.user_id, user.name, s.skill_name, s.type , q.qualification, j.job_name, s_url
from username "user"
  join lateral ( 
     select * 
     from skills s 
     where s.skill_user = user.user_id -- only for the "outer" user
     limit 1 -- only one skill for that user
  ) as s on true
  ... follow the same pattern for the other tables ...

The on true is necessary because a join needs a join condition but as we limit the rows for the current user in the derived table, there is no need to repeat that condition in the join condition.