pwalter83 (1/11/2013)
ChrisM@Work (1/11/2013)
I think this better fits the spec:
SELECT SUM(teu) AS TEU
FROM NCV_BL ncv
JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD
JOIN MG_VSLVOY_PORT_CONTROL vpc
ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
WHERE NOT EXISTS (
SELECT 1
FROM MG_VSLVOY_SCHEDULE vs
WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
)
AND BL_ID = '17231410'
Thanks for your reply.
Your query shows the SUM of TEU as NULL, it should be 4. Am I missing something ?
Can't tell without access to your data. This is how I'd do it. Work your way through the tables, using sometablealias.BL_ID = '17231410'.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden