I have the author and phrase 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 "pedro" and all "pedro's phrase"?
I tried with inner join but without success!
SELECT * FROM author INNER JOIN phrase ON author.autor_id = sentence.autor_id
What filters the result by a given condition is the WHERE clause.
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 in SQL Fiddle .
If you prefer to find by any name that has pedro in the middle, you can use:
WHERE
autor.autor_nome LIKE '%pedro%'
See working on SQL Fiddle .
Remember that in MySQL INNER JOIN is the same as CROSS JOIN , here are some tips on the possible uses of JOIN :