Join with two tables and a varchar2 field

Question

Hello. I am using oracle 11 g.

I’m trying to search a state table for a particular city.

For example, the state of Bahia has several cities. If Salvador is informed, he must return to the state of Bahia. The query I am trying to do is:

select * from estado as e join cidade as c on e.id_estado=c.fk_id_cidade on c.cidade='Salvador';

Gives error that the command has been closed improperly.

If I do:

select * from estado as e join cidade as c on e.id_estado=c.fk_id_cidade;

It works, but it brings several cities. If I passed a city id, I would find it, but the search parameter is by the name of the city.

Answer 1

I did so:

select*from estado as e join cidade as c on e.id_estado=c.fk_id_cidade and c.cidade='Salvador';

A variable can be passed as a parameter to the city attribute.

Answer 2

SELECT * 
FROM ESTADO E INNER JOIN CIDADE C
ON (E.id_estado = C.fk_id_cidade)
WHERE C.cidade LIKE @pNomeCidade ; 

Answer 3

To filter a result by a condition outside the JOIN is used WHERE.

Taking advantage, you can simplify so:

SELECT * FROM cidade AS c, estado AS e
  WHERE e.id_estado=c.fk_id_cidade AND c.cidade='Salvador';

If filtering after a grouping, use HAVING in place of WHERE.