Select in two tables at the same time

Question

I have the author and sentence tables:

autor:
autor_id | autor_nome
1        | joão
2        | pedro

frase:
frase_id | frase_frases    |autor_id 
1        | frase do joão   | 1
2        | frase do pedro  | 2
3        | frase do pedro  | 2
4        | frase do pedro  | 2

How do I display the name "peter" and all "peter’s phrase" ?

I tried with Inner Join but unsuccessfully!

SELECT * FROM author INNER JOIN phrase ON author.autor_id = phrase.autor_id

Answer 1

What filters the result by a certain condition is the clause WHERE.

If you want to select by name:

SELECT
   autor.autor_nome,
   frase.frase_frases
FROM
   autor
INNER JOIN
   frase ON autor.autor_id = frase.autor_id
WHERE
   autor.autor_nome = 'pedro'

See working on SQL Fiddle.

If you want to select by id:

SELECT
   autor.autor_nome,
   frase.frase_frases
FROM
   autor
INNER JOIN
   frase ON autor.autor_id = frase.autor_id
WHERE
   autor.autor_id = 2

See working on SQL Fiddle.

If you prefer to trace by any name you have pedro in the middle, you can use it like this:

WHERE
   autor.autor_nome LIKE '%pedro%'

See working on SQL Fiddle.

Remembering that in Mysql the INNER JOIN is the same as CROSS JOIN, here are some tips on the possible uses of JOIN:

What is the difference between INNER JOIN and OUTER JOIN?