database - 在 3 个表之间选择条件

Question

我有 3 个带有名称city和state的表project

城市

id_city |  name_city         
   1    |   JED
   2    |   RYD
   3    |   DMM

状态

带有 city.city_id 的列 city_id 外键

 id_state | name_state | city_id
     1    |    JED 1   | 1(JED)
     2    |    RYD 1   | 2(RYD)
     3    |    RYD 2   | 2(RYD)

项目

列 state_from_city_id_table_state 外键状态为 .city_id

id_project | city_id | state_from_city_id_table_state
      1    |     1   |  1(JED)>> (JED1)     question here 
      2    |     2   |  2(RYD)>> (RYD 1 or2) question here 

现在的问题是我选择了......就像1 ，city_id from project table我只想看看city_id from state table number 1 without number 2state_id from table project

像这样JED > JED1 only i don't wanna see RYD1 and RYD2

标签： databasesqliteconditional-statements

Answer 1

SELECT ct.*, st.*, pr.* FROM `city` ct LEFT JOIN `state` st ON ct.`id_city` = st.`state_id` LEFT JOIN `project` pr ON ct.`id_city` = pr.`city_id`