Home Forums SQL Server 2005 SQL Server 2005 General Discussion Check NOT EXISTS against two tables RE: Check NOT EXISTS against two tables

  • May 11, 2010 at 7:45 am

    #1165538

    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!