Relationship between tables varying according to value

Question

Relationship between tables varying according to value

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have a table that can write id fence (FK) in more than one column.

I need to make a select that links to the first reference column and if it is null, make the link through the second column.

My query:

SELECT
  * 
FROM nfs n
  , nfs_item ni
  , pedido_entrega pe 
WHERE n.id = ni.nfs_id 
  AND ni.pedido_entrega_id = pe.id

See that nfs_item is related to pedido_entrega through pedido_entrega_id . However, sometimes this column will be null and the reference will be written to a column named pedido_entrega_id_origem .

So I'll time these tables through ni.pedido_entrega_id = pe.id and time I'll relate them through ni.pedido_entrega_id_origem = pe.id . How do I do this?

sql

asked by anonymous 21.12.2017 / 19:56

3 answers

Pass input parameter to function Multiples Scheduled Tasks in WSO2

score 1 · Answer 1

I used JOIN and in the ON clause I check for both columns if the reference exists or if the value is null:

SQLFiddle - Online Example

SELECT 
  *
FROM 
  nfs n
JOIN nfs_item ni
  ON n.id = ni.nfs_id
JOIN pedido_entrega pe
  ON (
    ni.pedido_entrega_id = pe.id
    OR ni.pedido_entrega_id IS NULL
  )
  AND (
    ni.pedido_entrega_id_origem = pe.id
    OR ni.pedido_entrega_id_origem IS NULL
  )

score 0 · Answer 2

You can use the famous OR logic (well known in the if).

The 'OR' I usually use a lot in postgresql, so I do not guarantee it will work in your case. for me and the community help you better, edit your question and put a photo of the tables and also tell me the database that you are using ...
How do not know if you are using pgsql, mysql, mssql I go just put the same JOIN part. follow the code:

SELECT .... FROM nfs_item ni 
INNER JOIN nome_tabela pe ON ((ni.pedido_entrega_id = pe.id) OR (ni.pedido_entrega_id_origem = pe.id))

Of course, since I do not know which bank you're using, I could not test the code, but the logic for what you want to do is this. (maybe if you have any more business rules, you only have to change to LEFT JOIN or if you do not want to show null values, you just put a where and so)

score 0 · Answer 3

I was able to solve with the following people. I made a select after from just to link to my tables:

SELECT * FROM nfs n , (select request_id_id || request_identifier_id_id_id, id item_nfs_id from nfs_item) nfs_item_link , nfs_item ni , order delivery WHERE and n.id = ni.nfs_id and nfs_item_vinculo.item_nfs_id = ni.id and nfs_item_vinculo.entrega_id = pe.id

Thank you guys.