join with two tables and a varchar2 field

Navigation
1

Hello. I'm using oracle 11g.

I'm trying to search a status table for a certain city.

For example, the state of Bahia has several cities. If you are informed of Salvador, you should return Bahia state. The query I'm 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';

It gives the error that the command was terminated improperly.

If I do: 

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

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

    
asked by anonymous 27.11.2016 / 20:32

3 answers

0

To filter a result by a condition out of JOIN is used WHERE .

Enjoying, you can simplify it like this: 

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

If you are filtering after a pool, you use HAVING instead of WHERE .

    
27.11.2016 / 20:34
0

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.

    
01.12.2016 / 18:34
0
SELECT * 
FROM ESTADO E INNER JOIN CIDADE C
ON (E.id_estado = C.fk_id_cidade)
WHERE C.cidade LIKE @pNomeCidade ;
    
01.12.2016 / 18:54
Random button with isEmpty (); Count lines with PHP and JS (Different)