SQL テーブル結合時に対象カラムの値に応じて結合レコードを変える方法

Question

前提・実現したいこと

ユーザを管理しているテーブルとユーザの所属している部署を管理しているテーブルがあり以下のような構造になっています。
user_table

id	name
1	山田
2	田中
3	中村

busyo _table
|id|user_id|busyo_name|main_busyo|
|:--|:--:|:--:|
|1|1|総務|true|
|2|2|営業|false|
|3|3|総務|false|
|4|2|経理|true|
|5|2|営業|false|
|6|2|経理|false|

ユーザの一覧を表示する際user_table.id = busyo_table.user_idでテーブルを結合し
busyo _tableからmain_busyo がtrueのものを優先して取得しtrueのものがなければidの一番古いものを取得し結合したいと思っているのですが
SQLをあまり触ったことがない為うまくいきません。
どうか知恵をお貸しください。

補足情報（FW/ツールのバージョンなど）

DBはpostgreSQLを利用しています。

Answer 1

サブクエリしているやつ

SELECT u.*, b.* FROM user_table u, busyo_table b,
(SELECT id,
  (SELECT id from busyo_table where user_id=t1.id
             order by main_busyo desc, id limit 1) sq
   FROM user_table t1) c
WHERE u.id=c.id AND b.user_id=c.id AND b.id=c.sq;

Comments

[programKuma]

回答してくださった皆さんありがとうございます。
おかげさまでどうにかうまくできそうです。

Answer 2

色々な記述パターンはありますが、比較的高速と思われるrow_number()を使用するパターン。

select *
from (
      select *
           , row_number() over(partition by user_id order by main_busyo desc, id) as seq
      from busyo_table 
     ) as t1 left join user_table t2
     on t1.user_id=t2.id
where t1.seq=1

Answer 3

可能かと思います。

サブクエリ で検索してみてください。