ChrisM@home (1/13/2013)

---

Hi Paul

Your sample data set doesn't have a matching row between MG_VSLVOY_HEADER and MG_VSLVOY_PORT_CONTROL:

SELECT ncv.*, vh.VSLVOY_HEADER_ID

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'

Here's your sample data script for those who don't like attachments:

-- sample data script

CREATE TABLE [dbo].[NCV_BL](

[BL_ID] [decimal](10, 0) NOT NULL,

[POL_LOCATION_CD] [nvarchar](5) NULL,

[POD_LOCATION_CD] [nvarchar](5) NULL,

[SAISAN_VESSEL_CD] [nvarchar](10) NULL,

[SAISAN_VOYAGE_CD] [nvarchar](12) NULL,

[SAISAN_LEG_CD] [nvarchar](1) NULL,

[TEU] [decimal] (10,3) null,

[PLACE_OF_RECEIPT] [nvarchar](5) NULL,

[PLACE_OF_DELIVERY] [nvarchar](5) NULL,

[SAISAN_MONTH] [nvarchar](6) NULL

) ON [PRIMARY]

INSERT INTO [NCV_BL]

VALUES ('17231410','DKAAR','IDJKT','HNJAFR','0002E','E', '4','DEYTN','EGPSW','200802')

CREATE TABLE [dbo].[MG_VSLVOY_PORT_CONTROL](

[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,

[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL

) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_PORT_CONTROL]

VALUES ('1629990', '1342190'),('1629991', '1342191'),('1629992', '1342192'),

('1629993', '1342193'),('1629994', '1342194'),('1629995', '1342195'),

('1629996', '1342196'),('1629997', '1342197'),('1630001', '1342201'),

('1629998', '1379563'),('1629999', '1379564'),('1630000', '1379565')

CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](

[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,

[PORT_CD] [varchar](5) NOT NULL

) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_SCHEDULE]

VALUES ('1342190','DEHAM'),('1342191','NLRTM'),('1342192','FRLEH'),('1342193','ESALG'),('1342194','EGSUZ'),('1342195','SGSIN'),

('1342196','CNYTN'),('1342197','HKHKG'),('1342201','CNSHA'),('1379563','CNTXG'),('1379564','KRKAN'),('1379565','KRPUS')

CREATE TABLE [dbo].[MG_VSLVOY_HEADER](

[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,

[VESSEL_CD] [nvarchar](10) NOT NULL,

[VOYAGE_NUM] [nvarchar](12) NULL,

[LEG_CD] [nvarchar](1) NULL

) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_HEADER]

VALUES ('762476','HNJAFR','0002E','E'),('696131','COSHEL','0003W','W'),('669246','HJPA','0007R','R'),('761970','ALSAB','0008Q','Q')