Clash Royale CLAN TAG#URR8PPP

Filtering records city and state

I have 2 tables with name = city and state

city

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


state

id_state | id_for_city | name_state
1 | 1 | JED1
2 | 1 | JED2
3 | 2 | RUH1
4 | 2 | RUH2


I used ComboBox and i have 2

first combobox1 select name_city (it's ok )

second combobox2 i want select name_state through id_for_city but it left join id_city (here it,s not okay )

how i can write query by using left join in java ?

my code :

frst comboBox1 i think it,s ok

public void Filecombo()

try

String sql = "select name_city from city";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next())

options.add(rs.getString("name_city"));


comboCity.setItems(options);

pstmt.close();
rs.close();

catch (SQLException e)

e.printStackTrace();






second comboBox2 (here probloem)

public void Filecombo2()

try

String sq2 = " select name_state from state left join city on city.id_city= state.id_from_city";

pstmt2 = conn.prepareStatement(sq2);

rs = pstmt2.executeQuery();

while (rs.next())

options2.add(rs.getString("name_state"));



comboBranch.setItems(options2);
pstmt.close();
rs.close();

catch (SQLException e)

e.printStackTrace();






the result If i want to select different cities like RUH or DMM or JED
combobox2 will appear Anything related combobox1

java

javafx

sqlite

id_city

name_city

id_city

select name_state from state where id_state = aCityIdFromTheFirstQuery

1 Answer
1

Your question does not seem to have much to do with Java, and I'll assume that your JDBC code is basically working, and you are already getting a result set, albeit perhaps not exactly what you want. I think you just need to add a WHERE clause to your query:

WHERE

SELECT c.name_state
FROM state s
INNER JOIN city c
ON c.id_city = s.id_for_city
WHERE
s.name_city = 'JED';


Note that I replaced the left join with an inner join, since you want state names only, which come from the city table. A left join would be desirable if you wanted to return a NULL for the case of states which did not match to anything in the other table. But, that doesn't appear to be the case here.

city

NULL

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.