I have a drug chart.
In it I have:
There is the possibility of having more than one drug code for the same ans code.
I need to make an appointment to bring all medications that have the same ans code and different drug code.
Oracle usage.
In this specific case, it was not clear if it is really about self-relationship. Maybe you should change your question a bit to get more details.
But, speaking of your query, try using GROUP BY with the HAVING class.
SELECT codigo_medicamento, codigo_ans
FROM medicamentos
GROUP BY codigo_ans HAVING COUNT(codigo_medicamento) > 1
We count how many codigo_medicamento different we have for each codigo_ans and filter only those where the count is greater than 1 .
I can not test with ORACLE , but try and return on error.