1

I am working on a complex sql query. Just for explaning purpose of my issue i have reduced to the below short query:

 select a.column1 as field1,b.column2 as field 2,c.column3 as field3,COALESCE(SUM(d.paid_amt) OVER (PARTITION BY a.some_column),0)  as amount_paid  
   from a Inner join b on b.column3=ac.olumn2
   right join c on c.column4=b.column1 
   left join d on d.column2=a.column1 and d.column5 = a.column1
   where ...//some conditions

SO i am quite sure whats happening here. I am more concerned on the last left join on d table. if the d table has no records which matches d.column5 = a.column1 then i am not getting any results.

But i am trying to write the query in such a way that if d table retuns any values with the where condition d.column5 = a.column1 then i want to use those values or else if there are no records in d table i just want to get the records from the result of previous joins and get the records that i need.

Here with the current query the problem is as expected, if the join doesnt match the where condition its eliminating all the records. I want to have the records no matter what if the where condition satisfies or not.

I am not quite sure how to do this in one single query. Any suggestions are appreciated.

Note: @scaisEdge and @Zaynul Answer works fine. but the problem is the amount caluclation if i move the condition in line on join and clause. The am ount calculation should also be on the same condition d.column5 = a.column1.So i am not really sure how to overcome this :(

Sample date below:

field1 | field2 | field3 | ampount_paid | some_column_to_match
--------------------------------------------------------------
name   | value1 | other1 | 100          | 1
name1  | value2 | other2 | 100          | 1
name2  | value3 | other3 | 100          | 2

So i have just added the last columns to explain how i want the sum. I want sum the fields only if the some_column_to_match matches. So i am trying to get the output like:

field1 | field2 | field3 | amount_paid | some_column_to_match
--------------------------------------------------------------
name   | value1 | other1 | 200          | 1
name1  | value2 | other2 | 200          | 1
name2  | value3 | other3 | 100          | 2

So basically the calculation should be the sum of all values in amount_paid where some_column_to_match value matches. So in the above example, the first two columns should return 200 as it as 100 in each individual field for the same value.

ging
  • 117
  • 8

1 Answers1

0

If you want a left join then you should not use columns of left joined table (d.column5 = a.column1) in where condition in this way the join became an inner join
In these case add the condition for the columns related to the left join table at the corresponding ON clase 

select a.column1
  ,b.column2
  ,c.column3
  ,d.column4 
from a Inner join b on b.column3=ac.olumn2 
right join c on c.column4=b.column1 
left join d on d.column2=a.column1  AND  d.column5 = a.column1
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks will try this and post the result here – ging Mar 31 '19 at 15:44
  • Thanks this works as expected. But this results in another problem. I have updated the question accordingly. I am trying to sum the values fo some column in d table based on some condition. This is resulting incorrect values which i dont need. Sorry if its not clear please let me know i will try to correct and explain it better. – ging Mar 31 '19 at 15:56
  • Change the answer once you have obtained the solution is not fair .. SO is not a coding service .is a question answer site so .instead of change you question .. you should rate my or another right answer in proper way and post you new question in a new separated question ..... – ScaisEdge Mar 31 '19 at 16:07
  • Sorry i dont mean to do that. I just changed the questions as i felt like i havent explained the question properly in the first place. Thats my bad.I can revert the question and start a new thread if you think thats how it needs to be. And as said your first solution was a gem and what i am expecting from the first post. :( – ging Mar 31 '19 at 16:12
  • @ging thanks .. and comment me the link of your new question .. so i can try to eval this question too – ScaisEdge Mar 31 '19 at 16:15
  • As disccussed, created this thread: https://stackoverflow.com/questions/55442937/calculating-the-sum-of-a-field-based-on-other-join-table-columns Can you please have a look once? – ging Mar 31 '19 at 16:22
  • please let me know if anything is unclear for you from the other thread please – ging Mar 31 '19 at 16:38