CODEWITHSUNDEEP
postgresql
Rajarshi Das
Rajarshi Das

Reputation: 12320

SQL Query two fetch multiple table data by a conditional of an table

I have four table sa, at, ar, lv,

Sa table consist

|Name|
|s1|
|s2|
|s3|

at table consist

|Name|
|t1|
|t2|
|t3|

ar table consist

|Name|
|r1|
|r2|
|r3|

lv table consust

|Name|
|l1|
|l2|
|l3|

Now I have 2 tables pf, pfs

pf table consist

|sa| at| ar| lv
|s1| t1| r1| l1|

Now if pf table has any value 'All' then it will update all combination of others table data into pfs

like sa has 'All'

  |sa| at| ar| lv|
  |All| t1| r1| l1

Then pfs table should have

  |sa| at |ar| lv|
  |s1| t1| r1| l1|
  |s2| t1 | r1| l1|
  |s3| t1| r1| l1|

in this way if pf consist sa = 'All' and at = 'All' then all combination of s1,s2,s3 and t1,t2,t3 into pfs

Can you please advise me how can I do it more elegant way?

I know I need to write a trigger on pf .

Any hep will really be appreciable.

Upvotes: 0

Views: 67

Answers (1)

klin
klin

Reputation: 121604

The query:

select 
    coalesce(sa.name, sa) as sa,
    coalesce(at.name, at) as at,
    coalesce(ar.name, ar) as ar,
    coalesce(lv.name, lv) as lv
from pf
left join sa on pf.sa = 'All'
left join at on pf.at = 'All'
left join ar on pf.ar = 'All'
left join lv on pf.lv = 'All';

returns expected combinations. Use it in a trigger or as standalone query, according to your needs.

Upvotes: 1

Related Questions