Relationship between tables varying according to value

Asked

Viewed 55 times

1

I have a chart that can record the id reference (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 the nfs_item relates to pedido_entrega through the pedido_entrega_id. However, sometimes this column will be of value null and the reference will be recorded in a column called pedido_entrega_id_origem.

Therefore, time I will relate these tables through the ni.pedido_entrega_id = pe.id and time I will relate them through the ni.pedido_entrega_id_origem = pe.id. How do I do that?

sql

  • Is there any case where Table 1 has the two columns filled (id_table1 and id_source ) ?

  • No, she’ll either have the info on one or the other, never both together.

  • All right, I’ll set it right here and let you know, thanks in advance.

  • Hey, good morning, it didn’t work out. I’m going to put the query here, I think it helps understanding: select * from nfs n, nfs_item ni, pedido_delivery pe Where n.id = ni.nfs_id and ni.pedido_entrega_id = pe.id See that nfs_item relates to the pedi_delivery through the pedi_entrega_id. However, sometimes this column is empty and this id is saved in a column called pedi_entrega_id_origin. Therefore, time I will relate these tables through the "ni.pedido_entrega_id = pe.id" and time I will relate them through the "ni.pedido_entrega_id_origin = pe.id". How do I do that?

  • I changed my answer, I also did a simulation online and it worked. See if it fits you.

  • Put the database q vc ta using and if possible show us the tables or at least their fields....

  • Use Oracle database, but managed to solve, I will complement with a reply. Thanks for the help.

Show 2 more comments

3 answers

1

I used the JOIN and in the clause ON i check for both columns if the reference exists or if and 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
  )

0

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

The 'OR' I usually use a lot in postgresql, so I don’t guarantee it will work in your case. for me and the community to help you better, edit your question and put a picture of the tables and also tell me the database you are using...
As I do not know if you are using pgsql, mysql, mssql I will just put the JOIN part even. 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 don’t know which bank you’re using, I couldn’t test the code, but the logic for what you want to do is this. (maybe if you have an extra business rule you can change to LEFT JOIN or if you don’t want to show null values you can just put a Where and such)

0

I was able to work it out with the following guys. I made a select after from only to link with my tables:

SELECT * FROM nfs n , (select pedido_entrega_id||pedido_entrega_id_origem entrega_id, id item_nfs_id from nfs_item) nfs_item_vinculo , nfs_item ni , pedido_delivery pe WHERE and n.id = ni.nfs_id and nfs_item_vinculo.item_nfs_id = ni.id and nfs_item_link.entrega_id = pe.id

Personal thank you.

Browser other questions tagged sql

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