logic help

• xmanjeff

  SSC Rookie

  Points: 30

  More actions

  March 29, 2016 at 5:35 pm

  #399943

  I have these 3 tables to work with. #Master_Products and the #Shipping_Codes have a one to many relationship to #tblMasterShipping.

  In the #tblMasterShipping table I have possibility of having a prod_code with every ship_code. So, in the example i could have 3 records for Prod_code "Prod 1" in the #tblMasterShipping with 3 different ship_code values. I am trying to figure out how to determine what Prod_code records are not in the #tblMasterShipping with each of the possible ship_code values.

  so, does the following records exist or not in the #tblMasterShipping

  Prod_Code Ship_Code Base_Price

  1 1 Null

  1 2 Null

  1 3 Null

  CREATE TABLE #tblMasterShipping(

  [Prod_Code] [char](8) NOT NULL,

  [Ship_Code] [smallint] NOT NULL,

  [Base_Price] [money] NULL)

  CREATE TABLE #Shipping_Codes(

  [Ship_Code] [smallint] NOT NULL,

  [Descr] [varchar](50) NOT NULL,

  [Active] [char](1) NOT NULL)

  CREATE TABLE #Master_Products(

  [Prod_Code] [char](8) NOT NULL,

  [Base_Component] [smallint] NOT NULL,

  [Descr] [varchar](35) NOT NULL)

  Insert into #Shipping_Codes select 1,'Code 1','1'

  Insert into #Shipping_Codes select 2,'Code 2','1'

  Insert into #Shipping_Codes select 3,'Code 3','1'

  Insert into #Master_Products select 'Prod 1','1','Product Code 1'

  Insert into #Master_Products select 'Prod 2','1','Product Code 2'

  Insert into #Master_Products select 'Prod 3','1','Product Code 3'

  Insert into #tblMasterShipping select 'Prod 1',1,1

• pietlinden

  SSC Guru

  Points: 63551

  More actions

  March 29, 2016 at 6:12 pm

  #1868444

  Sounds like you can get the answer by using a cross join and then an EXCEPT or an outer join... but one question first...

  Do your tables have primary (unique) keys? What are they? (Just helps a lot when other people are trying to recreate your problem!)

  Here's what I *think* your structure is:

  use tempdb;

  GO

  /*

  DROP TABLE #MasterShipping;

  DROP TABLE #Shipping_Codes;

  DROP TABLE #Master_Products;

  GO

  */

  -- create tables...

  CREATE TABLE #MasterShipping(

  [Prod_Code] [char](8),

  [Ship_Code] [smallint],

  [Base_Price] [money] NULL

  CONSTRAINT pkMasterShipping PRIMARY KEY (Prod_code,Ship_code));

  CREATE TABLE #Shipping_Codes(

  [Ship_Code] [smallint],

  [Descr] [varchar](50) NOT NULL,

  [Active] [char](1) NOT NULL

  CONSTRAINT pkShippingCode PRIMARY KEY (Ship_Code));

  CREATE TABLE #Master_Products(

  [Prod_Code] [char](8) NOT NULL,

  [Base_Component] [smallint] NOT NULL,

  [Descr] [varchar](35) NOT NULL

  CONSTRAINT pkProduct PRIMARY KEY (Prod_Code));

  GO

  -- populate tables... now everybody starts in the same place!

  Insert into #Shipping_Codes VALUES (1,'Code 1','1');

  Insert into #Shipping_Codes VALUES (2,'Code 2','1');

  Insert into #Shipping_Codes VALUES (3,'Code 3','1');

  Insert into #Master_Products VALUES ('Prod 1','1','Product Code 1');

  Insert into #Master_Products VALUES ('Prod 2','1','Product Code 2');

  Insert into #Master_Products VALUES ('Prod 3','1','Product Code 3');

  Insert into #MasterShipping VALUES ('Prod 1',1,1);

  Okay, is this the answer?

  SELECT p.Prod_Code

  , s.Ship_Code

  FROM #Master_Products p

  CROSS JOIN #Shipping_Codes s

  WHERE NOT EXISTS (SELECT 1

  FROM #tblMasterShipping ms

  WHERE ms.Prod_Code = p.Prod_Code

  AND ms.Ship_Code = s.Ship_Code)

  ORDER BY p.Prod_Code

  , s.Ship_Code;

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  March 29, 2016 at 9:18 pm

  #1868456

  Or..

  SELECT T.prod_code,

  sc.ship_code

  FROM #shipping_codes sc

  LEFT JOIN #mastershipping ms

  ON ms.ship_code = sc.ship_code

  CROSS APPLY (SELECT prod_code

  FROM #master_products p

  WHERE p.prod_code <> Isnull(ms.prod_code, ''))T

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server