T-sql – Combining (joining) tables with no key

t-sql

I have table B and I have Table A. Table B contains records with dates I need to pull. However, I only want to pull them if:

  • They do not exist at all in table A
  • They exist in table A, but only when table A.status = 0

The trick is I have no key to just join the tables with. So, I am using the Item column and the date column to join. (Notice two items can have the same date. An there can also be multiple of the same items, but with different dates as well.)

So table B might have:

    [Item]      [Qty]   [Date]
    F-001100,   400      7/14/2016
    F-002200,   750      7/28/2016
    F-000767,   1000     7/28/2016
    F-006006,   300      8/01/2016
    F-002200,   900      7/30/2016

Table A might have:

[Item]    [Status]  [Qty]    [Date0]     [Date2]     [Date3]     [Date4]
F-001100, 1,        400      7/14/2016   7/15/2016   7/16/2016   7/17/2016
F-002200, 0,        750      7/28/2016   7/29/2016   7/30/2016   7/31/2016

So the result set should be:

[Item]       [Qty]     [Date]
F-002200,    750       7/28/2016
F-000767,    1000      7/28/2016
F-006006,    300       8/01/2016
F-002200,    900       7/30/2016   <--- I can't get this to show up!!

The matching works like this:

WHERE A.[Item] = B.[Item]
AND (   A.[Date1] = B.[Date]
     OR A.[Date2] = B.[Date]
     OR A.[Date3] = B.[Date]
     OR A.[Date4] = B.[Date] )

Best Answer

It looks like your two conditions can be reduced to just one:

  • They do not exist in table A with a status of 1.

Based on that, your SQL query could look like this:

SELECT
  B.*
FROM
  B
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      A
    WHERE
      A.Status = 1
      AND B.Item = A.Item
      AND B.Date IN (A.Date1, A.Date2, A.Date3, A.Date4)
  )
;

So, if table A has no match on Item and Date at all, the NOT EXISTS predicate will evaluate to True. If there is a match and its status is 0, the predicate will again be true because of the Status condition.

Related Question