SQL：検索対象とは別のテーブルを参照した値の取得、表示させる方法

Question

前提・実現したいこと

こんにちは。お世話になります。プログラミング初心者です。
現在私はOracleとJavaを使った検索画面の作成課題を行っています。
その検索(SELECT)を行うためのSQL文の作成に詰まってしまい、自分で調べてもいまいちわからなかったので質問させていただきます。

まずテーブルの構成ですが、検索対象となるメインのテーブルには名前と出身地や所属の番号の情報が、そして番号に紐づけする形で出身地や所属の名称情報が入ったサブテーブルが複数存在しています。
メインテーブルのカラム名に対応する別テーブルを参照することで名称がわかるイメージです。
例えば、メインテーブルの出身地カラムには1,2,3…とそれぞれ番号のみが入っていて、別テーブルに１は東京、２は千葉…といった番号と名称の情報が格納されています。

検索や表示結果には名称を使いたいと考えています。
名前、または所属や出身地名で検索をし、その結果を一覧で取得します。
そのための記述方法を教えていただきたいです。
(SELECT指定はサブテーブルのカラムですが、FROM指定はメインテーブルを、そして検索条件でメインとサブを紐付けて参照するイメージでいます…)

取得イメージは下記のような感じです。(例……出身地：東京で検索をした場合)

名前 　　　　 所属　　　　 出身地

---

田中太郎 営業部 東京
佐藤一郎 人事部 東京 etc...

:
:

また可能であれば、メインテーブルのカラム順に検索結果を一度に取得できたらと思っていますが、まとめて抽出できるsqlの記述方法などはありますでしょうか？（欲張りですみません）
知恵を貸していただけたらと思います、よろしくお願いします。

テーブル構成

テーブル名
MainData
カラム名　　 データタイプ　 　 他
1 ID int 主キー
2 FulName varchar
3 DepartmentID int 外部キー
4 StateID int 外部キー

テーブル名
DepartmentData
カラム名　　 データタイプ　　 他
1 DepartmentID int 主キー
2 DepartmentName varchar

テーブル名
StateData
カラム名　　 データタイプ　　 他
1 StateID int 主キー
2 StateName varchar

###　テーブル作成とデータ挿入のSQL文

--テーブルの作成
CREATE TABLE MainData (
ID int,
FullName varchar(30),
DepartmentID int,
StateID int);

CREATE TABLE DepartmentData (
DepartmentID int,
DepartmentName varchar(30));

CREATE TABLE StateData (
StateID int,
StateName varchar(30));

--主キーの設定
ALTER TABLE MainData
ADD CONSTRAINT PK_ID PRIMARY KEY (ID);

ALTER TABLE DepartmentData
ADD CONSTRAINT PK_DID PRIMARY KEY (DepartmentID);

ALTER TABLE StateData
ADD CONSTRAINT PK_SID PRIMARY KEY (StateID);

--外部キーの設定
ALTER TABLE MainData 
ADD FOREIGN KEY(DepartmentID) 
REFERENCES DepartmentData(DepartmentID);

ALTER TABLE MainData 
ADD FOREIGN KEY(StateID) 
REFERENCES StateData(StateID);


--データの挿入(仮データです）
INSERT INTO DepartmentData VALUES (1,'営業部');
INSERT INTO DepartmentData VALUES (2,'人事部');
INSERT INTO DepartmentData VALUES (3,'広告部');

INSERT INTO StateData VALUES (1,'東京');
INSERT INTO StateData VALUES (2,'神奈川');
INSERT INTO StateData VALUES (3,'千葉');


INSERT INTO MainData VALUES (1,'田中太郎',1,1);
INSERT INTO MainData VALUES (2,'佐藤一郎',2,1);
INSERT INTO MainData VALUES (3,'鈴木花子',3,3);

INSERT INTO DepartmentData VALUES (1,'営業部');
INSERT INTO DepartmentData VALUES (2,'人事部');
INSERT INTO DepartmentData VALUES (3,'広告部');

INSERT INTO StateData VALUES (1,'東京');
INSERT INTO StateData VALUES (2,'神奈川');
INSERT INTO StateData VALUES (3,'千葉');

試したこと

まずはSELECT結果が数字ではなく文字で表示できるようにしたいと考えて調べた結果、結合を使って表示できるということは分かったのですが(下記参照)、これだと格納されたデータの一覧表示となってしまい検索ができず、行いたいこととは見当違いなことをしている気がして、混乱してしまっています。

SQL文

SELECT
a.FullName, b.DepartmentName, c.StateName
from MainData a
left outer join DepartmentData b on a.DepartmentID = b.DepartmentID
left outer join StateData c on a.StateID = c.StateID ;

＊備忘録用追記

検索条件を付ける際、
自分ではこのように記述をしてエラーを返されていました。
今見ると自分でも不思議ですし、先輩方にも笑われてしまいそうですね。

問題の記述

--その１
SELECT a.FullName, b.DepartmentName, c.StateName
from MainData a
where a.FullName ='佐藤一郎'
     left outer join DepartmentData b on a.DepartmentID = b.DepartmentID
     left outer join StateData c on a.StateID = c.StateID ;

--その２
SELECT a.FullName, b.DepartmentName, c.StateName
from MainData a
where c.StateName IN (select c.StateName from MainData a
     left outer join StateData c on a.StateID = c.StateID );

Comments

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEされている必要があります。質問にテーブル定義の説明もCREATE TABLEで、同じようにSELECTされるデータもINSERTされている必要がありますから、INSERT文でhttps://teratail.com/help#about-markdown の [コードを入力] で載せておくとコメントが付き易いです。

[Orlofsky]

https://docs.oracle.com/cd/F19136_01/sqlrf/Data-Types.html#GUID-DF7E10FC-A461-4325-A295-3FD4D150809E
>VARCHARデータ型は、使用しないでください。かわりにVARCHAR2データ型を使用してください。

[syk00]

コメントありがとうございます。
すでにテーブル作成とデータ挿入を終えているのを前提で話をしていたので、そちらも追って記します。
データ型については、今回のものは課題で実用的なものではないため、このままvarcharとさせてください。
また別のものを作成することになった際には気に留めておこうと思います。

[Orlofsky]

信用を無くすだけかと。

[syk00]

何に対して信用を無くすと仰られているのでしょうか？
コードの追記に関してはコメントをいただいたので改めて記述するために打ち込んでいたところですし(自身の練度では素早く打ち込めないため追記しますと返しました)、
データ型にしても、現在作成しているものに関しては実用のものではないため、実用を想定するにしても次回のデータ作成時から気を付ければいいものと判断いたしました。

Answer 1

検索や表示結果には名称を使いたいと考えています

WHERE 条件で名称を指定して下さい。
例えば、出身地を条件にする場合は以下になります。

SELECT a.FullName, b.DepartmentName, c.StateName
from MainData a
     left outer join DepartmentData b on a.DepartmentID = b.DepartmentID
     left outer join StateData c on a.StateID = c.StateID 
where c.StateName = '東京'

※複数の条件の場合にANDやORで条件を増やしたり、likeによるあいまい検索などは仕様に合わせて下さい。

Comments

[syk00]

回答でいただいた通りに記述して試してみたらうまくできました！ありがとうございます！