Select to check where a primary key is referenced

1

I have the following tables:

asked by anonymous 12.11.2016 / 14:15

1 answer

2

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
    
13.11.2016 / 00:54