Select to check where a primary key is referenced

Asked

Viewed 795 times

1

I have the following tables:

_________PERSON_________

serial id PK

name varchar(50) NOT NULL

email varchar(100) NOT NULL

tel varchar(14) NOT NULL

_________CLIENT__________

integer person FK/PK --(FK to id in tbl PESSOA)

rg varchar(20) NOT NULL

Cpf varchar(14) NOT NULL

_________DEPENDENT________

integer person FK/PK --(FK to id in tbl PESSOA)

integer guarantor FK NOT NULL-(FK to person in tbl CLIENT)

authorized Boolean NOT NULL

_________STAFF________

integer person FK/PK --(FK to id in tbl PESSOA)

root Boolean NOT NULL

A person can be either an employee or a customer or a dependent or the three.

I want to make a select that tells me in which of the three tables the PERSON ID is used as a foreign key, i.e., whether a person is a client, a client/employee, a client/dependent/employee, etc.

The ideal return form can be an array of chars with the initial Lette of each table (e.g. {C, D, F}), or a string composed of the initial letter of each 'CDF'. I could create a column tipo of the kind char(1)[] on the table PESSOA so every time a person is referenced in any of the three tables, the person table would have the field tipo updated by inserting the initial letter corresponding to the table. But if it can be solved with a select, I think it would be more practical.

postgresql
  • 1

    See if this solves your problem: http://pastebin.com/KhkBW05G

  • It wasn’t exactly that, but it helped find the way. I don’t really need the person’s name to come back, I just really need the lyrics. I also need to make this consultation for a specific person. A clause was enough WHERE at the end of each JOIN. Thus, if, for example, the person with ID 1 is referenced in the three tables (Client, Dependent, Employee) the query will return three lines ('C', ’D', 'F'). If it is only in Customer and Employee, the query will return two lines ('C', 'F'). So on. If the person is not in any, the return is empty.

  • If you want to do with subselect, then you will return a row with only 3 columns

  • How would it be with subselect?

1 answer

2


Making INNER JOIN only the records that satisfy the junction will appear, so 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 it will 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

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.