３つのテーブルを結合して、最小値の列があるレコードを取得する方法

Question

###３つのテーブルを結合して、最小値の列があるレコードを取得する方法

勉強でなっとくいくSQLができなくて悩んでいます( ；∀；)

###データベース
以下のテーブルがあります。

ユーザーテーブル：users_table (id,user_name)
所属情報テーブル：user_rel_table (user_id,dept_id,kubun)
所属テーブル：dept_table (id,dept_name,dept_address)

関係はこんな感じです
ユーザーテーブル<-n対n->所属情報テーブル<-n対1->所属テーブル

上記の３つのテーブルを結合するさい、所属情報テーブルに同じuser_idがある場合は、kubunが最小値のレコードのdept_idでdept_tableからデータを取得したいのですが全然できなくて悩んでいます。

###試したこと

-- 単純に結合するだけ
SELECT 
  u.id,
    u.user_name,
    ur.user_id,
    ur.dept_id,
    ur.kubun,
    d.dept_name,
    d.dept_address
FROM users_table u
LEFT JOIN user_rel_table ur
	ON u.id = ur.user_id
LEFT JOIN dept_table d
	ON d.id = ur.dept_id

さらに結合する際、所属情報テーブルに複数同じuser_idがある場合、
kubunが最小値のレコードのdept_idでdeptと関連したいのですがなかなかうまく行きません。
どうすればよろしいでしょうか？？？

SELECT
    u.id,
    u.name,
    ur.name,
    ur.user_id,
    ur.dept_id,
    ur.min,
    d.dept_name,
    d.dept_ryakusyo
    
FROM users_table u
LEFT JOIN (
	SELECT
        ur.name,
		ur.user_id,
		ur.dept_id,
  	    min(kubn) AS min
    FROM  user_rel_table
	GROUP BY user_id
	) ur
	ON ur.user_id =u.id 
LEFT JOIN dept_table d
	ON d.id = ur.dept_id
 

###補足情報(言語/FW/ツール等のバージョンなど)
サーバのバージョン： 10.1.22-MariaDB - mariadb.org binary distribution

Answer 1

兼務がある場合で、主の所属を取り出したいということですね。
ポイントは、users_tableから相関副問い合わせで、kubunの最小値を取り出しておくことで、
後は結合していくだけ。

select 
    uv.id,
    uv.user_name,
    ur.dept_id,
    ur.kubun,
    d.dept_name,
    d.dept_address
from (
    select *
         ,(select min(kubun) from user_rel_table where user_id=u.id ) as kubun
    from users_table u
) uv
left join user_rel_table ur
  on uv.id=ur.user_id and uv.kubun=ur.kubun
left join dept_table d 
  on d.id=ur.dept_id

最終的にkubunが不要なら、相関副問い合わせでdept_idを求めておけば、user_rel_table との結合は不要になります。

select 
    uv.id,
    uv.user_name,
    d.dept_id,
    d.dept_name,
    d.dept_address
from (
    select *
         ,(select dept_id from user_rel_table 
           where user_id=u.id order by kubun limit 1) as dept_id
    from users_table u
) uv
left join dept_table d 
  on d.id=uv.dept_id

相関副問い合わせを、where条件で使用する方法もあります。

SELECT 
    u.id,
    u.user_name,
    ur.user_id,
    ur.dept_id,
    ur.kubun,
    d.dept_name,
    d.dept_address
FROM users_table u
LEFT JOIN user_rel_table ur
    ON u.id = ur.user_id
LEFT JOIN dept_table d
    ON d.id = ur.dept_id
where ur.kubun is null
   or ur.kubun=(select min(kubun) from user_rel_table where user_id=u.id)

Comments

[pg_Ruu]

こういうことがしたかったのです！！　すごくたすかりました！！！
Mr.sazi ありがとうございます！！！！！！！