Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My code is ok... result is...

poslovni_partner    UL. roba    ULR. ostalo    UL. Repromaterijal      Saldo  
MOZZART  DOO          630.20        93.60            292.50           1016.30  


Show me data just if exist in all 3 tables...if not exist dont show...

If not exist in one table to show 0.00

Some help?

What I have tried:

SQL
select x1.poslovni_partner,   
CONVERT(decimal(16,2),(ISNULL(x1.ULR,0))) AS 'UL. roba',   
CONVERT(decimal(16,2),(ISNULL(x2.ULRO, 0))) as 'ULR. ostalo',   
CONVERT(decimal(16,2),(ISNULL(x3.ULRM, 0))) as 'UL. Repromaterijal',  
CONVERT(decimal(16, 2), (ISNULL(ULR, 0) + (ISNULL(ULRO, 0) + (ISNULL(ULRM, 0))))) as 'Saldo'    
from (select poslovni_partner, sum(iznos_sa_porezom) as ULR from(select poslovni_partner as poslovni_partner,  iznos_sa_porezom from dbo.mp_ulazni_racun_roba_lista where redni_broj  IS NOT NULL) a group by poslovni_partner) as x1 JOIN  
(select poslovni_partner, sum(iznos_sa_porezom) as ULRO from (select poslovni_partner as poslovni_partner,iznos_sa_porezom from dbo.mp_ulazni_racun_ostalo_lista where redni_broj  IS NOT NULL) b group by poslovni_partner) as x2 on x1.poslovni_partner = x2.poslovni_partner JOIN  
(select poslovni_partner, sum(iznos_sa_porezom) as ULRM from (select poslovni_partner as poslovni_partner,iznos_sa_porezom from dbo.mp_ulazni_racun_repromaterijal_lista where redni_broj  IS NOT NULL) b group by poslovni_partner) as  x3 on x1.poslovni_partner = x3.poslovni_partner  
Posted
Updated 22-Jul-19 17:57pm
v2
Comments
MadMyche 20-Jul-19 12:34pm    
First thing you need to do is format this mess.
Meysam Toluie 23-Jul-19 7:07am    
Instead of Join, you can use Left Join; Then with the help of ISNULL function you can change null values to ZERO

1 solution

I change my code

select x1.poslovni_partner, 
CONVERT(decimal(16,2),(ISNULL(x1.ULR,0))) AS 'UL. roba', 
CONVERT(decimal(16,2),(ISNULL(x2.ULRO, 0))) as 'ULR. ostalo', 
CONVERT(decimal(16,2),(ISNULL(x3.ULRM, 0))) as 'UL. Repromaterijal',
CONVERT(decimal(16, 2), (ISNULL(ULR, 0) + (ISNULL(ULRO, 0) + (ISNULL(ULRM, 0))))) as 'Saldo'  
from (select poslovni_partner, sum(iznos_sa_porezom) as ULR from(select poslovni_partner as poslovni_partner,  iznos_sa_porezom from dbo.mp_ulazni_racun_roba_lista where redni_broj  IS NOT NULL) a group by poslovni_partner) as x1 FULL OUTER JOIN
(select poslovni_partner, sum(iznos_sa_porezom) as ULRO from (select poslovni_partner,iznos_sa_porezom from dbo.mp_ulazni_racun_ostalo_lista where redni_broj  IS NOT NULL) b group by poslovni_partner) as x2 on x1.poslovni_partner = x2.poslovni_partner FULL OUTER JOIN
(select poslovni_partner, sum(iznos_sa_porezom) as ULRM from (select poslovni_partner,iznos_sa_porezom from dbo.mp_ulazni_racun_repromaterijal_lista where redni_broj  IS NOT NULL) b group by poslovni_partner) as  x3 on x3.poslovni_partner = x1.poslovni_partner AND  x1.poslovni_partner = x3.poslovni_partner AND x2.poslovni_partner = x3.poslovni_partner AND x3.poslovni_partner = x2.poslovni_partner


If not exist in table1 name of partner write null
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900