Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 LEFT JOIN trouble

Author  Topic 

jeusdi
Starting Member

27 Posts
Posted - 2007-07-19 : 02:35:28
Hello forum , I'm interested in solving LEFT JOIN problem:
I've made 3 sentences with LEFT JOIN, and then, and each one I've put in one sentence.
When I perform each sentence one by one, they works correctly. But when I execute the last one the results are diferent and I don't know why it occurs.

SENTENCE N1:
SELECT A.MATERIAL, A.AMPLADA, M.NOM, SUM(BM.METRES) AS STOCK, SUM(BM.METRES) * A.AMPLADA / 1000 AS METRES_QUADRATS_STOCK, COUNT(BM.BOBINA) AS BOBINES_STOCK
FROM ARTICLES A
LEFT OUTER JOIN MATERIAL M ON (A.MATERIAL = M.MATERIAL)
LEFT OUTER JOIN BOBINES_MATERIAL BM ON (A.MATERIAL = BM.MATERIAL AND A.AMPLADA = BM.AMPLADA AND BM.BOBINA NOT IN (SELECT BOBINA FROM BOBINES_OTS) AND BM.DATA = (SELECT MAX(DATA) FROM BOBINES_MATERIAL WHERE BOBINA = BM.BOBINA) AND BM.METRES > 0)
GROUP BY A.MATERIAL, A.AMPLADA, M.NOM
ORDER BY A.MATERIAL, A.AMPLADA

Result-->
1.......125.......VELLUM/RP51/HG65.......NULL.......NULL.......0
1.......150.......VELLUM/RP51/HG65.......NULL.......NULL.......0
1.......167.......VELLUM/RP51/HG65.......156438.......26125.......79
1.......180.......VELLUM/RP51/HG65.......36150.......6507.......18
1.......200.......VELLUM/RP51/HG65.......109336.......21867.......59
1.......220.......VELLUM/RP51/HG65.......92530.......20356.......47
1.......230.......VELLUM/RP51/HG65.......61350.......14110.......31
1.......240.......VELLUM/RP51/HG65.......NULL.......NULL.......0
1.......250.......VELLUM/RP51/HG65.......18865.......4716.......10
1.......260.......VELLUM/RP51/HG65.......1500.......390.......1
1.......330.......VELLUM/RP51/HG65.......NULL.......NULL.......0

-------------------------------------------------------------------------------------

2nd sentence:

SELECT A.MATERIAL, A.AMPLADA, M.NOM, SUM(CM.METRES_LINEALS_BOBINA * CM.BOBINES) AS STOCK_DEMANAT, SUM(CM.METRES_LINEALS_BOBINA * CM.BOBINES) * A.AMPLADA / 1000 AS METRES_QUADRATS , SUM(CM.BOBINES) AS BOBINES_DEMANADES
FROM ARTICLES A
LEFT OUTER JOIN MATERIAL M ON (A.MATERIAL = M.MATERIAL)
LEFT OUTER JOIN COMANDES_MATERIAL CM ON (A.MATERIAL = CM.MATERIAL AND A.AMPLADA = CM.AMPLADA)
GROUP BY A.MATERIAL, A.AMPLADA, M.NOM
ORDER BY A.MATERIAL, A.AMPLADA

Result-->
1.......125.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......150.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......167.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......180.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......200.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......220.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......230.......VELLUM/RP51/HG65.......44000.......10120.......22
1.......240.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......250.......VELLUM/RP51/HG65.......16000.......4000.......8
1.......260.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL
1.......330.......VELLUM/RP51/HG65.......NULL.......NULL.......NULL

-----------------------------------------------------------------------------------------------------------

3th setence:

SELECT A.MATERIAL, A.AMPLADA, M.NOM, SUM(PREVISIO_METRES_PREPARACIO) + SUM(PREVISIO_METRES_PRODUCCIO) AS STOCK_RESERVAT_OTS, SUM(PREVISIO_METRES_PREPARACIO) + SUM(PREVISIO_METRES_PRODUCCIO) / 1000 * A.AMPLADA AS METRES_QUADRATS_RESERVAT_OTS
FROM ARTICLES A
LEFT OUTER JOIN MATERIAL M ON (A.MATERIAL = M.MATERIAL)
LEFT OUTER JOIN PLANNING P ON (A.MATERIAL = P.MATERIAL AND A.AMPLADA = P.AMPLADA)
GROUP BY A.MATERIAL, A.AMPLADA, M.NOM
ORDER BY A.MATERIAL, A.AMPLADA

Result-->
1.......125.......VELLUM/RP51/HG65.......1300.0.......188.75
1.......150.......VELLUM/RP51/HG65.......NULL.......NULL
1.......167.......VELLUM/RP51/HG65.......30216.0.......5937.3820000000005
1.......180.......VELLUM/RP51/HG65.......NULL.......NULL
1.......200.......VELLUM/RP51/HG65.......5385.0.......1325.0
1.......220.......VELLUM/RP51/HG65.......20026.0.......4795.7200000000003
1.......230.......VELLUM/RP51/HG65.......52085.0.......12349.15
1.......240.......VELLUM/RP51/HG65.......NULL.......NULL
1.......250.......VELLUM/RP51/HG65.......2577.0.......666.75
1.......260.......VELLUM/RP51/HG65.......NULL.......NULL
1.......330.......VELLUM/RP51/HG65.......NULL.......NULL

-----------------------------------------------------------------------------------------------------------

Sentence 3 in 1:

SELECT A.MATERIAL, A.AMPLADA, M.NOM, SUM(BM.METRES) AS STOCK, SUM(BM.METRES) / 1000 * A.AMPLADA AS METRES_QUADRATS_STOCK, COUNT(BM.BOBINA) AS BOBINES_STOCK
, SUM(CM.METRES_LINEALS_BOBINA * CM.BOBINES) AS STOCK_DEMANAT, SUM(CM.METRES_LINEALS_BOBINA * CM.BOBINES) / 1000 * A.AMPLADA AS METRES_QUADRATS , SUM(CM.BOBINES) AS BOBINES_DEMANADES
, SUM(PREVISIO_METRES_PREPARACIO) + SUM(PREVISIO_METRES_PRODUCCIO) AS STOCK_RESERVAT_OTS, SUM(PREVISIO_METRES_PREPARACIO) + SUM(PREVISIO_METRES_PRODUCCIO) / 1000 * A.AMPLADA AS METRES_QUADRATS_RESERVAT_OTS
FROM ARTICLES A
LEFT JOIN MATERIAL M ON (A.MATERIAL = M.MATERIAL)
LEFT JOIN BOBINES_MATERIAL BM ON (A.MATERIAL = BM.MATERIAL AND A.AMPLADA = BM.AMPLADA AND BM.BOBINA NOT IN (SELECT BOBINA FROM BOBINES_OTS) AND BM.DATA = (SELECT MAX(DATA) FROM BOBINES_MATERIAL WHERE BOBINA = BM.BOBINA) AND BM.METRES > 0)
LEFT JOIN COMANDES_MATERIAL CM ON (A.MATERIAL = CM.MATERIAL AND A.AMPLADA = CM.AMPLADA)
LEFT JOIN PLANNING P ON (A.MATERIAL = P.MATERIAL AND A.AMPLADA = P.AMPLADA)
GROUP BY A.MATERIAL, A.AMPLADA, M.NOM
ORDER BY A.MATERIAL, A.AMPLADA

Result-->
1.......125.......VELLUM/RP51/HG65.......NULL.......NULL.......0.......NULL.......NULL.......NULL.......1300.0.......188.75
1.......150.......VELLUM/RP51/HG65.......NULL.......NULL.......0.......NULL.......NULL.......NULL.......NULL.......NULL
1.......167.......VELLUM/RP51/HG65.......2659446.......444053.......1343.......NULL.......NULL.......NULL.......2387064.0.......469053.17800000001
1.......180.......VELLUM/RP51/HG65.......36150.......6480.......18.......NULL.......NULL.......NULL.......NULL.......NULL
1.......200.......VELLUM/RP51/HG65.......218672.......43600.......118.......NULL.......NULL.......NULL.......317715.0.......78175.0
1.......220.......VELLUM/RP51/HG65.......370120.......81400.......188.......NULL.......NULL.......NULL.......941222.0.......225398.84
1.......230.......VELLUM/RP51/HG65.......368100.......84640.......186.......8184000.......1882320.......4092.......1614635.0.......382823.65000000002
1.......240.......VELLUM/RP51/HG65.......NULL.......NULL.......0.......NULL.......NULL.......NULL.......NULL.......NULL
1.......250.......VELLUM/RP51/HG65.......18865.......4500.......10.......160000.......40000.......80.......25770.0.......6667.5
1.......260.......VELLUM/RP51/HG65.......1500.......260.......1.......NULL.......NULL.......NULL.......NULL.......NULL
1.......330.......VELLUM/RP51/HG65.......NULL.......NULL.......0.......NULL.......NULL.......NULL.......NULL.......NULL

PSamsig
Constraint Violating Yak Guru

384 Posts
Posted - 2007-07-20 : 05:25:35
This is proberly what you are looking for:
SELECT A.MATERIAL
	,A.AMPLADA
	,M.NOM

	,DBM.STOCK
	,DBM.STOCK / 1000 * A.AMPLADA AS METRES_QUADRATS_STOCK
	,DBM.BOBINES_STOCK

	,DCM.STOCK_DEMANAT
	,DCM.STOCK_DEMANAT / 1000 * A.AMPLADA AS METRES_QUADRATS
	,DCM.BOBINES_DEMANADES

	,DP.STOCK_RESERVAT_OTS
	,DP.STOCK_RESERVAT_OTS / 1000 * A.AMPLADA AS METRES_QUADRATS_RESERVAT_OTS
FROM ARTICLES A 
LEFT JOIN MATERIAL M
	ON (A.MATERIAL = M.MATERIAL)

LEFT JOIN (
	SELECT MATERIAL, AMPLADA
		  ,SUM(BM.METRES) AS STOCK
		  ,COUNT(BM.BOBINA) AS BOBINES_STOCK
	FROM BOBINES_MATERIAL BM
	WHERE BM.BOBINA NOT IN (SELECT BOBINA FROM BOBINES_OTS) AND
			 BM.DATA = (SELECT MAX(DATA) FROM BOBINES_MATERIAL WHERE BOBINA = BM.BOBINA) AND
				 BM.METRES > 0)
) DBM
	ON (DBM.MATERIAL = A.MATERIAL AND DBM.AMPLADA = A.AMPLADA)

LEFT JOIN (
	SELECT MATERIAL, AMPLADA
		  ,SUM(CM.METRES_LINEALS_BOBINA * CM.BOBINES) AS STOCK_DEMANAT
		  ,SUM(CM.BOBINES) AS BOBINES_DEMANADES
	FROM COMANDES_MATERIAL CM
	GROUP BY MATERIAL, AMPLADA
) DCM
	ON (DCM.MATERIAL = A.MATERIAL AND DCM.AMPLADA = A.AMPLADA)

LEFT JOIN (
	SELECT MATERIAL, AMPLADA
		  ,SUM(P.PREVISIO_METRES_PREPARACIO) + SUM(P.PREVISIO_METRES_PRODUCCIO) AS STOCK_RESERVAT_OTS
	FROM PLANNING P
	GROUP BY MATERIAL, AMPLADA
) DP
	ON (DP.MATERIAL = A.MATERIAL AND DP.AMPLADA = A.AMPLADA)
ORDER BY A.MATERIAL, A.AMPLADA


-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site