SELECT
A.league_id,
A.league_name,
A.display_order,
A.prefecture_nc,
A.gender_nc,
A.create_user,
TO_CHAR(A.create_date,'YYYY/MM/DD HH24:MI:SS') AS create_date,
A.update_user,
TO_CHAR(A.update_date,'YYYY/MM/DD HH24:MI:SS') AS update_date,
A.delete_flag,
B.user_name AS create_user_name,
C.user_name AS update_user_name
FROM league_master A
LEFT OUTER JOIN user_info B ON A.create_user = B.user_id
LEFT OUTER JOIN user_info C ON A.update_user = C.user_id
WHERE league_id = 7; クエリ実行エラー
SELECT
A.league_id AS league_id,
A.clubteam_id AS clubteam_id,
A.clubteam_name AS clubteam_name,
A.clubteam_kana AS clubteam_kana,
A.prefecture_id AS prefecture_id,
A.gender AS gender,
A.create_user AS create_user,
TO_CHAR(A.create_date, 'YYYY/MM/DD HH24:MI:SS') AS create_date,
A.update_user AS update_user,
TO_CHAR(A.update_date, 'YYYY/MM/DD HH24:MI:SS') AS update_date,
A.delete_flag AS delete_flag,
B.league_name AS league_name,
B.display_order AS display_order,
B.prefecture_nc AS prefecture_nc,
B.gender_nc AS gender_nc,
C.prefecture AS prefecture_name,
D.user_name AS create_user_name,
E.user_name AS update_user_name,
CASE WHEN F.witness_cnt IS NULL THEN 0 ELSE F.witness_cnt END AS witness_cnt
FROM clubteam_master A INNER JOIN league_master B ON A.league_id = B.league_id
LEFT OUTER JOIN prefecture_master C ON A.prefecture_id = C.prefecture_id
LEFT OUTER JOIN user_info D ON A.create_user = D.user_id
LEFT OUTER JOIN user_info E ON A.update_user = E.user_id
LEFT OUTER JOIN (
SELECT
A.clubteam_id AS clubteam_id,
COUNT(*) AS witness_cnt
FROM
((SELECT
A.game_id AS game_id,
C.clubteam_id AS clubteam_id
FROM
user_witness A
INNER JOIN game_master B ON A.game_id = B.game_id
INNER JOIN clubteam_master C ON B.team1_id = C.clubteam_id
) UNION ALL (
SELECT
A.game_id AS game_id,
C.clubteam_id AS clubteam_id
FROM
user_witness A
INNER JOIN game_master B ON A.game_id = B.game_id
INNER JOIN clubteam_master C ON B.team2_id = C.clubteam_id
)) A
GROUP BY A.clubteam_id
) F ON A.clubteam_id = F.clubteam_id
WHERE A.league_id = AND A.prefecture_id = 4
ORDER BY clubteam_kana ASC; クエリ実行エラー
SELECT prefecture FROM prefecture_master WHERE prefecture_id=4;
のチーム一覧
