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

Question

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.

klin · Accepted Answer · 2016-10-02 16:05:18Z

1

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.

edited Oct 2, 2016 at 16:05

answered Oct 2, 2016 at 15:59

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related