Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check NOT EXISTS against two tables


Check NOT EXISTS against two tables

Author
Message
donato1026
donato1026
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 140
Hello,

I have three tables

TableA
TableB
TableC

I want to check for rows (that do not exists) in TableA against TableB, however, I want to check with criteria from TableB and TableC as well.

How would I do this?

Thank you,

D
Lowell
Lowell
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19006 Visits: 39444
it's actually easy..you can use multiple AND/OR statements with an IF:

IF NOT EXISTS(SELECT 1 FROM TABLEA WHERE ID=1)
AND NOT EXISTS(SELECT 1 FROM TABLEB WHERE ID=1)
BEGIN
--do stuff
END



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026
donato1026
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 140
Lowell (5/10/2010)
it's actually easy..you can use multiple AND/OR statements with an IF:

IF NOT EXISTS(SELECT 1 FROM TABLEA WHERE ID=1)
AND NOT EXISTS(SELECT 1 FROM TABLEB WHERE ID=1)
BEGIN
--do stuff
END



I actually want to show the rows in TableB, if they do not exist in TableA, however, I need to join TableC and TableB in a way to check and see if a condition is met within TableC and TableB, such as say TableC.fieldname = 'Y' and TableB.fieldname is null
Lowell
Lowell
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19006 Visits: 39444
the logic is the same...it's just the test condition that changes:
don't think you wan t not exists...you want to confirm that there is at least one row on TableC that is not in tableB:

IF EXISTS (SELECT 1 FROM TableC LEFT OUTER JOIN ON TABLEC.ID = TABLEB.ID WHERE TableC.fieldname = 'Y' and TableB.fieldname is null)





Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026
donato1026
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 140
Lowell (5/10/2010)
the logic is the same...it's just the test condition that changes:
don't think you wan t not exists...you want to confirm that there is at least one row on TableC that is not in tableB:

IF EXISTS (SELECT 1 FROM TableC LEFT OUTER JOIN ON TABLEC.ID = TABLEB.ID WHERE TableC.fieldname = 'Y' and TableB.fieldname is null)





Thanks, Lowell. . . That's not working though. I know this is simple, but with everything I am trying, I'm not getting results I need.
Lowell
Lowell
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19006 Visits: 39444
pseudo code (TableA TableB etc) sux. i don't think it can describe a real resolution for you. moving concepts from pseudo code to your real issue is stopping you from getting a real resolution.

show us the real exact CREATE TABLE commands for your three tables...from that, we could identify the joins, and write the correct left outer joins to find missing data.

also, show us the code you are trying! that'll help a lot.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026
donato1026
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 140
Very well. . . I want to check aganist the WEB_CATALOG_IMAGES table with the Product table. If somehting is in the WEB_CATALOG_IMAGES table that IS NOT in the Product table, I want to know. However, it also needs to NOT have a drop_cd entererd in the ITM table. I want to join all three using SKU in the Product table, item_code in the WEB_CATALOG_IMAGES table and itm_cd in the ITM table. Thank you.

/****** Object: Table [dbo].[Product] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Product_ProductGUID] DEFAULT (newid()),
[Name] [nvarchar](400) NOT NULL,
[Summary] [ntext] NULL,
[Description] [ntext] NULL,
[SEKeywords] [ntext] NULL,
[SEDescription] [ntext] NULL,
[SpecTitle] [ntext] NULL,
[MiscText] [ntext] NULL,
[SwatchImageMap] [ntext] NULL,
[IsFeaturedTeaser] [ntext] NULL,
[FroogleDescription] [ntext] NULL,
[SETitle] [ntext] NULL,
[SENoScript] [ntext] NULL,
[SEAltText] [ntext] NULL,
[SizeOptionPrompt] [ntext] NULL,
[ColorOptionPrompt] [ntext] NULL,
[TextOptionPrompt] [ntext] NULL,
[ProductTypeID] [int] NOT NULL CONSTRAINT [DF_Product_ProductTypeID] DEFAULT ((1)),
[TaxClassID] [int] NOT NULL CONSTRAINT [DF_Product_TaxClassID] DEFAULT ((1)),
[SKU] [nvarchar](50) NULL,
[ManufacturerPartNumber] [nvarchar](50) NULL,
[SalesPromptID] [int] NOT NULL CONSTRAINT [DF_Product_SalesPromptID] DEFAULT ((1)),
[SpecCall] [ntext] NULL,
[SpecsInline] [tinyint] NOT NULL CONSTRAINT [DF_Product_SpecsInline] DEFAULT ((0)),
[IsFeatured] [tinyint] NOT NULL CONSTRAINT [DF_Product_IsFeatured] DEFAULT ((0)),
[XmlPackage] [nvarchar](100) NULL,
[ColWidth] [int] NOT NULL CONSTRAINT [DF_Product_ColWidth] DEFAULT ((4)),
[Published] [tinyint] NOT NULL CONSTRAINT [DF_Product_Published] DEFAULT ((1)),
[Wholesale] [tinyint] NOT NULL CONSTRAINT [DF_Product_Wholesale] DEFAULT ((0)),
[RequiresRegistration] [tinyint] NOT NULL CONSTRAINT [DF_Product_RequiresRegistration] DEFAULT ((0)),
[Looks] [int] NOT NULL CONSTRAINT [DF_Product_Looks] DEFAULT ((0)),
[Notes] [ntext] NULL,
[QuantityDiscountID] [int] NULL,
[RelatedProducts] [ntext] NULL,
[UpsellProducts] [ntext] NULL,
[UpsellProductDiscountPercentage] [money] NOT NULL CONSTRAINT [DF_Product_UpsellProductDiscountPercentage] DEFAULT ((0.0)),
[RelatedDocuments] [ntext] NULL,
[TrackInventoryBySizeAndColor] [tinyint] NOT NULL CONSTRAINT [DF_Product_TrackInventoryBySizeAndColor] DEFAULT ((0)),
[TrackInventoryBySize] [tinyint] NOT NULL CONSTRAINT [DF_Product_TrackInventoryBySize] DEFAULT ((1)),
[TrackInventoryByColor] [tinyint] NOT NULL CONSTRAINT [DF_Product_TrackInventoryByColor] DEFAULT ((1)),
[IsAKit] [tinyint] NOT NULL CONSTRAINT [DF_Product_IsAKit] DEFAULT ((0)),
[ShowInProductBrowser] [int] NOT NULL CONSTRAINT [DF_Product_ShowInProductBrowser] DEFAULT ((1)),
[IsAPack] [int] NOT NULL CONSTRAINT [DF_Product_IsAPack] DEFAULT ((0)),
[PackSize] [int] NOT NULL CONSTRAINT [DF_Product_PackSize] DEFAULT ((0)),
[ShowBuyButton] [int] NOT NULL CONSTRAINT [DF_Product_ShowBuyButton] DEFAULT ((1)),
[RequiresProducts] [ntext] NULL,
[HidePriceUntilCart] [tinyint] NOT NULL CONSTRAINT [DF_Product_HidePriceUntilCart] DEFAULT ((0)),
[IsCalltoOrder] [tinyint] NOT NULL CONSTRAINT [DF_Product_IsCalltoOrder] DEFAULT ((0)),
[ExcludeFromPriceFeeds] [tinyint] NOT NULL CONSTRAINT [DF_Product_ExcludeFromPriceFeeds] DEFAULT ((0)),
[RequiresTextOption] [tinyint] NOT NULL CONSTRAINT [DF_Product_RequiresTextOption] DEFAULT ((0)),
[TextOptionMaxLength] [int] NULL,
[SEName] [nvarchar](150) NULL,
[ExtensionData] [ntext] NULL,
[ExtensionData2] [ntext] NULL,
[ExtensionData3] [ntext] NULL,
[ExtensionData4] [ntext] NULL,
[ExtensionData5] [ntext] NULL,
[ContentsBGColor] [nvarchar](10) NULL,
[PageBGColor] [nvarchar](10) NULL,
[GraphicsColor] [nvarchar](20) NULL,
[ImageFilenameOverride] [ntext] NULL,
[IsImport] [tinyint] NOT NULL CONSTRAINT [DF_Product_IsImport] DEFAULT ((0)),
[IsSystem] [tinyint] NOT NULL CONSTRAINT [DF_Product_IsSystem] DEFAULT ((0)),
[Deleted] [tinyint] NOT NULL CONSTRAINT [DF_Product_Deleted] DEFAULT ((0)),
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Product_CreatedOn] DEFAULT (getdate()),
[PageSize] [int] NOT NULL CONSTRAINT [DF_Product_PageSize] DEFAULT ((20)),
[WarehouseLocation] [nvarchar](100) NULL,
[AvailableStartDate] [datetime] NOT NULL CONSTRAINT [DF_Product_AvailableStartDate] DEFAULT (getdate()),
[AvailableStopDate] [datetime] NULL,
[GoogleCheckoutAllowed] [tinyint] NOT NULL CONSTRAINT [DF_Product_GoogleCheckoutAllowed] DEFAULT ((1)),
[SkinID] [int] NOT NULL CONSTRAINT [DF_Product_SkinID] DEFAULT ((0)),
[TemplateName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Product_TemplateName] DEFAULT (''),
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


/****** Object: Table [dbo].[WEB_CATALOG_IMAGES] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WEB_CATALOG_IMAGES](
[item_code] [varchar](9) NOT NULL,
[style] [varchar](255) NULL,
[vendor_code] [varchar](4) NULL,
[thumbnail_description] [varchar](40) NULL,
[product_description] [varchar](150) NULL,
[description] [varchar](max) NULL,
[cover_description] [varchar](255) NULL,
[cover_number] [varchar](30) NULL,
[finish] [varchar](30) NULL,
[dim_length] [varchar](30) NULL,
[dim_width] [varchar](30) NULL,
[dim_height] [varchar](30) NULL,
[grade] [varchar](30) NULL,
[cubes] [varchar](30) NULL,
[wood_species] [varchar](255) NULL,
[cleaning_code] [varchar](255) NULL,
[fabric_content] [varchar](max) NULL,
[special_order] [varchar](1) NULL,
[colors_in_stock] [varchar](255) NULL,
[feature_1] [varchar](30) NULL,
[feature_2] [varchar](30) NULL,
[feature_3] [varchar](30) NULL,
[feature_4] [varchar](30) NULL,
[benefit_1] [varchar](30) NULL,
[benefit_2] [varchar](30) NULL,
[benefit_3] [varchar](30) NULL,
[benefit_4] [varchar](30) NULL,
[web_enabled] [varchar](1) NULL,
[eps_file_location] [varchar](255) NULL,
[created_by] [varchar](50) NULL,
[created_date] [datetime] NULL,
[modified_by] [varchar](50) NULL,
[modified_date] [datetime] NULL,
CONSTRAINT [PK_WEB_CATALOG_IMAGES] PRIMARY KEY CLUSTERED
(
[item_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/****** Object: Table [dbo].[ITM] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ITM](
[ITM_CD] [varchar](9) NOT NULL,
[COMM_CD] [varchar](3) NOT NULL,
[VE_CD] [varchar](4) NOT NULL,
[RELATED_ITM_CD] [varchar](9) NULL,
[MNR_CD] [varchar](5) NOT NULL,
[CAT_CD] [varchar](5) NULL,
[SUPER_CD] [varchar](9) NULL,
[ITM_TP_CD] [varchar](3) NOT NULL,
[SPEC_ORD_FLAG] [varchar](1) NULL,
[MEAS_CD] [varchar](1) NULL,
[RET_PRC] [decimal](13, 2) NOT NULL,
[REPL_CST] [decimal](13, 2) NOT NULL,
[LST_ACT_DT] [datetime] NOT NULL,
[VSN] [varchar](30) NULL,
[DES] [varchar](30) NULL,
[SIZ] [varchar](30) NULL,
[SIZ_ID] [varchar](12) NULL,
[SIZ_CD] [varchar](3) NULL,
[FINISH] [varchar](30) NULL,
[FINISH_ID] [varchar](12) NULL,
[FINISH_CD] [varchar](3) NULL,
[COVER] [varchar](30) NULL,
[COVER_ID] [varchar](12) NULL,
[COVER_CD] [varchar](3) NULL,
[GRADE] [varchar](30) NULL,
[GRADE_ID] [varchar](12) NULL,
[GRADE_CD] [varchar](3) NULL,
[UDF5] [varchar](30) NULL,
[UDF5_ID] [varchar](12) NULL,
[UDF5_CD] [varchar](3) NULL,
[UDF6] [varchar](30) NULL,
[UDF6_ID] [varchar](12) NULL,
[UDF6_CD] [varchar](3) NULL,
[UDF7] [varchar](30) NULL,
[UDF7_ID] [varchar](12) NULL,
[UDF7_CD] [varchar](3) NULL,
[DROP_DT] [datetime] NULL,
[PTAG_PRINT_QTY] [decimal](4, 0) NULL,
[CRPT_WID] [decimal](4, 0) NULL,
[SER_PCT] [decimal](7, 3) NULL,
[SMR_PCT] [decimal](7, 3) NULL,
[VOL] [decimal](13, 2) NULL,
[WEIGHT] [decimal](13, 2) NULL,
[PALLET_QTY] [decimal](4, 0) NULL,
[PO_LEAD_TIME] [decimal](4, 0) NULL,
[STAT_CD] [varchar](10) NULL,
[STAT_DT] [datetime] NULL,
[FAMILY_CD] [varchar](5) NULL,
[STYLE_CD] [varchar](5) NULL,
[SPIFF] [decimal](13, 2) NULL,
[RET_PRC_CHNG_DT] [datetime] NULL,
[ADV_PRC] [decimal](13, 2) NULL,
[IVC_CST] [decimal](13, 2) NULL,
[FRT_FAC] [decimal](7, 3) NULL,
[DAYS_WAR] [decimal](4, 0) NULL,
[WARRANTABLE] [varchar](1) NULL,
[EXC_DT] [datetime] NULL,
[LABEL_TP_CD] [varchar](1) NULL,
[PKG_SPLIT_METHOD] [varchar](1) NULL,
[PKG_CMPNT] [varchar](1) NULL,
[RCV_LABEL_CD] [varchar](3) NULL,
[INVENTORY] [varchar](1) NOT NULL,
[FGN_REPL_CST] [decimal](13, 2) NULL,
[FGN_DUTY_RATE] [decimal](7, 4) NULL,
[TREATABLE] [varchar](1) NOT NULL,
[DROP_CD] [varchar](3) NULL,
[PRC1] [decimal](13, 2) NULL,
[PRC1_CHNG_DT] [datetime] NULL,
[PRC3_CHNG_DT] [datetime] NULL,
[SETUP_REQ] [varchar](1) NULL,
[IN_CARTON] [varchar](1) NULL,
[GENERIC_SKU] [varchar](1) NULL,
[VSAL_QTY] [decimal](4, 0) NULL,
[POINT_SIZE] [decimal](5, 0) NULL,
[CALC_AVAIL] [varchar](1) NULL,
[CMDTY_CD] [varchar](7) NULL,
[SHOW_RLP_PRICES] [varchar](1) NULL,
[ALT_DES] [varchar](30) NULL,
[PU_DISC_PCNT] [decimal](7, 3) NULL,
[FRAN_MRKUP_PCNT] [decimal](7, 3) NULL,
[BATCH_TP_ITM] [varchar](1) NULL,
[BULK_TP_ITM] [varchar](1) NULL,
[USER_QUESTIONS] [varchar](80) NULL,
[MASTER_PACK_QTY] [decimal](3, 0) NULL,
[PRE_TREATED] [varchar](1) NULL,
[CELL_SAVE] [varchar](1) NULL,
[CELL_INC_CALC] [varchar](1) NULL,
[CELL_PHONE] [varchar](1) NULL,
[STOP_TIME] [decimal](4, 0) NULL,
[FRAME_TP_ITM] [varchar](1) NULL,
[OPTION_LIST_GRP] [varchar](10) NULL,
[AVAIL_PAD_DAYS] [decimal](4, 0) NULL,
[BLOCK_RETAIL] [varchar](1) NULL,
[WARR_DAYS] [decimal](4, 0) NULL,
[ALLOW_CREATE_CUST_ORD_OPTION] [varchar](1) NULL,
[SERIAL_TP] [varchar](1) NULL,
[CELL_TYPE] [varchar](1) NULL,
[RET_PLAN_CD] [varchar](3) NULL,
[RET_PLAN_EFF_DT] [datetime] NULL,
[USED_MERCH] [varchar](1) NULL,
[PUR_SLSP_CD] [varchar](10) NULL,
[CREATE_DT] [datetime] NOT NULL,
[NSP_AMT] [decimal](13, 2) NULL,
[TAX_RESP] [varchar](1) NULL,
[EXT_DES] [varchar](255) NULL,
CONSTRAINT [PK_ITM] PRIMARY KEY CLUSTERED
(
[ITM_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Thanks again. . .
Lowell
Lowell
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19006 Visits: 39444
man i love details! with your explanation, it looks so easy now!

I think i have what you want; run this select and confirm whether or not this is finding what you are looking for:
records in WEB_CATALOG_IMAGES not in Product and even iof there is a record in ITM, the Drop_cd in ITM must be NULL



--find all records in WEB_CATALOG_IMAGES not in Product and also no Drop_cd in ITM
SELECT
WEB_CATALOG_IMAGES.*
FROM WEB_CATALOG_IMAGES
LEFT OUTER JOIN PRODUCT
ON WEB_CATALOG_IMAGES.ITEM_CODE = PRODUCT.SKU
LEFT OUTER JOIN ITM
ON WEB_CATALOG_IMAGES.ITEM_CODE = ITM.ITM_CD
WHERE PRODUCT.PRODUCTID IS NULL --due to join, no record in Product
AND ITM_CD.DROP_CD IS NULL -- NOT have a drop_cd entered



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026
donato1026
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 140
Wow! That seems to do the trick! I had something similar, however, my join wasn't correct and hence why it wasn't working.

Thank you VERY much, Lowell!
donato1026
donato1026
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 140
Lowell (5/11/2010)
man i love details! with your explanation, it looks so easy now!

I think i have what you want; run this select and confirm whether or not this is finding what you are looking for:
records in WEB_CATALOG_IMAGES not in Product and even iof there is a record in ITM, the Drop_cd in ITM must be NULL



--find all records in WEB_CATALOG_IMAGES not in Product and also no Drop_cd in ITM
SELECT
WEB_CATALOG_IMAGES.*
FROM WEB_CATALOG_IMAGES
LEFT OUTER JOIN PRODUCT
ON WEB_CATALOG_IMAGES.ITEM_CODE = PRODUCT.SKU
LEFT OUTER JOIN ITM
ON WEB_CATALOG_IMAGES.ITEM_CODE = ITM.ITM_CD
WHERE PRODUCT.PRODUCTID IS NULL --due to join, no record in Product
AND ITM_CD.DROP_CD IS NULL -- NOT have a drop_cd entered



Ok, Lowell. . . Now. . .

Is there any way from here, (I know. . . I'm kind of a pain) that a report of some fasion can be emailed of products/records that are NOT in the Product table. In other words. . . Is there any way to email the results of this query? If so, how would I go about doing that?

Thank you again!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search