SQL select two values from the same column and display at the same time-CodePudding

I don't know know how to explain it better so let me give you my example.

This is the code I have:

SELECT nome,idc,nome_local,idade
FROM ciclista, local,parente
WHERE idade < 18
AND nome_local LIKE '%Covil%'
AND ciclista.idl_residencia=local.idl
AND ciclista.idc=parente.idc_b
AND parente.tipo LIKE 'm%'

I need to display another name (nome from ciclista where ciclista.idc=parente.ida_a). Basically I want to display the parent's name infront of the results.

CodePudding user response：

You can use the same table twice just by giving it another name. For getting the sons you name the table c1, for getting the parents you name it c2, then just apply the condition that you mentioned (ciclista.idc = parente.ida_a, e.g., c2.idc = parente.ida_a). The names can be whatever you want as long as they allowed names

SELECT c1.nome, c1.idc, nome_local, c1.idade, c2.nome
FROM ciclista c1, ciclista c2, local, parente
WHERE c1.idade < 18
AND nome_local LIKE '%Covil%'
AND c1.idl_residencia = local.idl
AND c1.idc = parente.idc_b
AND c2.idc = parente.ida_a
AND parente.tipo LIKE 'm%'