I have the following tables:
I have the following tables:
By doing INNER JOIN
only the records that satisfy the join will appear, then you can check the method to select them as follows:
SELECT pes.nome,
'C' as tipo
FROM pessoa pes
INNER JOIN cliente cli ON cli.pessoa = pes.id
UNION
SELECT pes.nome,
'D' as tipo
FROM pessoa pes
INNER JOIN dependente dep ON dep.pessoa = pes.id
UNION
SELECT pes.nome,
'F' as tipo
FROM pessoa pes
INNER JOIN funcionario fun ON fun.pessoa = pes.id
You can also write it with subquery
so you have 3 columns according to the type of person:
SELECT pes.nome,
(SELECT 1
FROM cliente cli
WHERE cli.pessoa = pes.id) as cliente
(SELECT 1
FROM dependente dep
WHERE dep.pessoa = pes.id) as dependente,
(select 1
FROM funcionario fun
WHERE fun.pessoa = pes.id) as funcionario
FROM pessoa pes