March 29, 2016 at 5:35 pm
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
March 29, 2016 at 6:12 pm
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;
March 29, 2016 at 9:18 pm
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply