0

I am trying to import a number of items into this system. Many of the items exist, but they need additional information added to them. I'm trying to exclude them if they already exist.

Here are two of the tables with sample data, notice the NULL in the vw_suppliers

Focus Item
-- inv_mast_uid, -- item_id, -- new_inv_mast_uid
    1234,       Widget 1,       4321
    2345,       Widget 2,       5432

vw_Suppliers
-- supplier_id, -- new_supplier_id, -- division
    987,            789,            789
    876,            NULL,           NULL
    765,            567,            567

Here is my SQL:

SELECT
    fi.item_id,
    fi.new_inv_mast_uid,
    bhs.new_supplier_id
    bhs.new_division_id

  FROM Focus_Item fi
    LEFT JOIN SourceSQL.dbo.inv_loc il
      ON il.inv_mast_uid = fi.inv_mast_uid
    LEFT JOIN DC_SQL.dbo.vw_suppliers bhs
      ON bhs.legacy_id = il.primary_supplier_id

    LEFT JOIN TargetSQL.dbo.inventory_supplier bh_iss
      ON bh_iss.inv_mast_uid = fi.bh_inv_mast_uid
      AND bh_iss.supplier_id = bhs.bh_supplier_id
      AND bh_iss.division_id = bhs.division_id
      AND bh_iss.delete_flag = 'N'

  WHERE 
    bhs.bh_supplier_id IS NOT NULL
    AND bh_iss.supplier_id IS NULL
    AND bh_iss.division_id IS NULL

So the inv_mast_uid, supplier_id and division need to be unique in the inventory_supplier table. If it already exists in the inventory_supplier table, then I want to exclude it from the list. I THOUGHT a LEFT JOIN would give me NULLs if it wasn't found, then I could exclude them with the IS NULL in the WHERE clause.

Items are in multiple locations, and locations can have multiple suppliers.

The above result seems to give me a MUCH smaller value than expecting. All inclusive I get about 1200 results. I'm expecting to remove 300-400 once I exclude the dupes, but I get like 300 results instead.

Is there a better way to exclude the dupes?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dizzy49
  • 1,360
  • 24
  • 35
  • Use sub query instead of `LEFT JOIN` – Fabio Aug 02 '19 at 23:22
  • 1
    Can you please add current output and expected output also. It will give more idea how output result data is getting mismatched. – ANaik Aug 02 '19 at 23:36
  • 1
    provide sample data from all your 4 input table and also expected output considering given sample data. – mkRabbani Aug 03 '19 at 08:06
  • @Fabio So do something like AND inv_mast_uid NOT IN (SELECT inv_mast_uid FROM inventory_supplier WHERE supplier_id=bhs.bh_supplier_id AND division=bhs.division_id AND delete_flag='N') ?? – Dizzy49 Aug 03 '19 at 23:06
  • @Dizzy49,did you tried that? – Fabio Aug 03 '19 at 23:11
  • @Fabio Not yet. I'm about to give it a try. – Dizzy49 Aug 03 '19 at 23:15
  • @Fabio I ended up using AND EXISTS(SELECT * FROM inv_supp WHERE uid=uid AND supp=supp AND div=div AND delete='N') **Shorted but you get the gist :D – Dizzy49 Aug 04 '19 at 00:00
  • Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Oct 15 '19 at 02:10
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular formatted initialization. – philipxy Oct 15 '19 at 02:12
  • @DaleBurrell There is nothing wrong with "bunch", there is no need to edit, this edit could be reasonably considered contrary to their intent. – philipxy Oct 15 '19 at 02:18
  • @philipxy I disagree "bunch" - is a US informal term not necessarily known to all speakers of English. – Dale K Oct 15 '19 at 02:24

0 Answers0