Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: Records not in table

    I have 3 tables, one is the items master, the other one is the vendor master and a third one that makes a relationship between both (which has more tha a million records). I need a script that tells me which records don't have a relationship. For example:


    Item master has a column ITEMCODE
    Vendor master has a column VENDORCODE
    third table has both these columns

    I did this:

    SELECT A.ITEMCODE, B.VENDORCODE FROM ITEMS A, VENDORS B WHERE A.ITEMCODE + B.VENDORCODE NOT IN (SELECT C.ITEMCODE + C.VENDORCODE FROM TABLE C)

    but since that table has more than a million records, the script ran for about 2 hours and never finished. any ideas?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    --Items without a relationship record
    SELECT i.item_code
    FROM   dbo.items
     LEFT
      JOIN dbo.vendor_items vi
        ON i.item_code = vi.item_code
    WHERE  vi.item_code IS NULL
    
    --Vendors without a relationship record
    SELECT v.vendor_code
    FROM   dbo.vendors v
     LEFT
      JOIN dbo.vendor_items vi
        ON v.vendor_code = vi.vendor_code
    WHERE  vi.vendor_code IS NULL
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SELECT ItemCode
    FROM Items
    EXCEPT
    SELECT ItemCode
    FROM VendorItems;

  4. #4
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    Hi, thanks for your reply, the thing is if I add a new vendor for example, and someone has added certain relationships to some items, but not to all of them. I wouldn't get any results with those queries.

    I have item A, B and vendor 1, 2, 3

    in the relationship table I have
    A, 1
    A, 2
    A, 3
    B, 3

    I need to know that B, 1 and B, 2 don't exist

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Every vendor supplies every item?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    no, that was just an example

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @items table (
       item_code char(1)
    )
    DECLARE @vendors table (
       vendor_code int
    )
    DECLARE @vendor_items table (
       item_code   char(1)
     , vendor_code int
    )
    
    INSERT INTO @items (item_code) VALUES ('A')
    INSERT INTO @items (item_code) VALUES ('B')
    
    INSERT INTO @vendors (vendor_code) VALUES (1)
    INSERT INTO @vendors (vendor_code) VALUES (2)
    INSERT INTO @vendors (vendor_code) VALUES (3)
    
    INSERT INTO @vendor_items (item_code, vendor_code) VALUES ('A', 1)
    INSERT INTO @vendor_items (item_code, vendor_code) VALUES ('A', 2)
    INSERT INTO @vendor_items (item_code, vendor_code) VALUES ('A', 3)
    INSERT INTO @vendor_items (item_code, vendor_code) VALUES ('B', 3)
    
    SELECT i.item_code
         , v.vendor_code
    FROM   @items i
     CROSS
      JOIN @vendors v
    
    EXCEPT
    
    SELECT item_code
         , vendor_code
    FROM   @vendor_items
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    excelent, that worked. thanks a lot

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think george just learned a new operator: EXCEPT

    nice move on the CROSS JOIN, too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I knew about it, but always opted to write the left-join-null, because I figured it was more portable (hey, I could be wayyyy wrong!)
    Quote Originally Posted by r937
    nice move on the CROSS JOIN, too

    Thank you kindly sir - I thought it was pretty neat
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •