Combining (joining) tables with no key

Question

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] )

Answer 1

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.

Answer 2

You can use NOT EXISTS for this:

SELECT *
FROM TableB b
WHERE NOT EXISTS (SELECT 1
                  FROM TableA a
                  WHERE a.Item = b.Item    
                   AND a.Date = b.Date
                   AND a.Status = 1
                 )

Since multiple entries can exist, NOT EXISTS can be simpler than a solution with JOIN. If your criteria were different and you wanted to evaluate the duplicates separately, ie only bringing back 1 of the 2 records for a item/date pair if it didn't exist in the other table, you could use ROW_NUMBER() to assign a row number for each row on each side for use in a join.

Answer 3

To solve this one, I did the following:

Created the tables:

CREATE TABLE tablea (skua VARCHAR(8), statusa INT, qtya INT, datea date);
CREATE TABLE tableb (skub VARCHAR(8), qtyb INT, dateb date);

Populated the tables:

tablea)

INSERT INTO tablea VALUES ('F-001100', 1, 400, '2016-07-14');
INSERT INTO tablea VALUES ('F-002200', 0, 750, '2016-07-28');

tableb)

INSERT INTO tableb VALUES ('F-001100',  400, '2016-07-14');
INSERT INTO tableb VALUES ('F-002200',  750, '2016-07-28');
INSERT INTO tableb VALUES ('F-000767', 1000, '2016-07-28');
INSERT INTO tableb VALUES ('F-006006',  300, '2016-08-01');

The simplest way I could see of doing this was to use the EXCEPT operator (SQL Server documentation also here):

SELECT skub, qtyb, dateb
FROM tableb
WHERE qtyb = qtyb -- "dummy" clause, replying to OP's question, gives same result but any valid `WHERE` clause will work
EXCEPT
SELECT skua, qtya, datea FROM tablea
WHERE statusa = 1
ORDER BY dateb ASC, qtyb ASC

Result (correct AFAICS):

skub        | qtyb|           dateb
---------   -------      ----------
F-002200    |  750|      2016-07-28
F-000767    | 1000|      2016-07-28
F-006006    |  300|      2016-08-01

Answer 4

Using FULL OUTER JOIN will allow you to return data from B where it doesn't exist in A.

I usually use FULL OUTER JOIN and COALESCE when returning Sales figures for new products which were not available in a prior time period:

SELECT COALESCE([A].[ITEM],[B].[ITEM]) AS [ITEM]
      --,ISNULL([A].[QTY],0) + ISNULL([B].[QTY],0) AS [SUM_QTY]
      ,COALESCE([A].[QTY],[B].[QTY]) AS [QTY]
      ,COALESCE([A].[DATE],[B].[DATE]) AS [DATE]
FROM [A]
FULL OUTER JOIN [B]
    ON [A].[ITEM] = [B].[ITEM]
   AND [A].[DATE] = [B].[DATE]
GROUP BY COALESCE([A].[ITEM],[B].[ITEM])
        ,COALESCE([A].[DATE],[B].[DATE])

This will return results from both tables, regardless of whether it exists in both. COALESCE ensures that both are taken into account, while iSNULL ensures that a NULL value is counted as 0.

**Note: QTY assumes the quantity can be readily converted to INT if the value is not NULL. Hopefully you don't have any A-Z characters in your QTY column, as you'd need additional handling for that scenario.

Answer 5

I'm adding a second answer, since the question has changed quite radically since it was first asked (check out the edits if you wish to follow the thread).

This covers edits 4 and 5 where the OP added to, and changed, considerable parts of the question.

New tablea and data as follows:

CREATE TABLE tablea (skua VARCHAR(8), statusa INT, qtya INT, datea0 date, datea1 date, datea2 date, datea3 date);


INSERT INTO tablea VALUES ('F-001100', 1, 400, '2016-07-14', '2016-07-15', '2016-07-16', '2016-07-17');
INSERT INTO tablea VALUES ('F-002200', 0, 750, '2016-07-28', '2016-07-29', '2016-07-30', '2016-07-31');

Plus the 900 datum for the 31/07/2016 was added to tableb - see edit 4.

INSERT INTO tableb VALUES ('F-002200',  900, '2016-07-30');

The new question can still be solved using the method I showed in the my first answer (CREATE TABLE scripts changed with as per new data) with the addition of a CTE (Common Table Expression) to essentially PIVOT the rows of the new tableA. As follows:

WITH tab1 AS -- tab1 `PIVOTS` the new tableA.
(
  (SELECT distinct (skua) AS sku, qtya, datea0 AS dt FROM tablea
  WHERE statusa = 1
  GROUP BY sku, dt, qtya)
  UNION
  (SELECT distinct (skua) AS sku, qtya, datea1 AS dt FROM tablea
  WHERE statusa = 1
  GROUP BY sku, dt, qtya)
  UNION
  (SELECT distinct (skua) AS sku, qtya, datea2 AS dt FROM tablea
  WHERE statusa = 1
  GROUP BY sku, dt, qtya)
  UNION
  (SELECT distinct (skua) AS sku, qtya, datea3 AS dt FROM tablea
  WHERE statusa = 1
  GROUP BY sku, dt, qtya)
)
SELECT skub, qtyb, dateb
FROM tableb
WHERE qtyb = qtyb
EXCEPT
SELECT sku, qtya, dt FROM tab1
ORDER BY dateb ASC, qtyb ASC

And the result is (again correct AFAICS):

skub        |qtyb|          dateb
----          ----     ----------
F-002200    | 750|     2016-07-28
F-000767    |1000|     2016-07-28
F-002200    | 900|     2016-07-30
F-006006    | 300|     2016-08-01

Answer 6

As others have mentioned, the main restriction is "SELECT FROM B where ITEM not in A with a status of 1". So get a list of A with a status of 1 and exclude that list:

use TEST;
go

CREATE TABLE A (Item VARCHAR(8), [Status] INT, [Qty] INT, [Date0] date, [Date2] date, [Date3] date, [Date4] date);
CREATE TABLE B (Item VARCHAR(8), [Qty] INT, [Date] date);

INSERT INTO A VALUES ('F-001100', 1, 400, '2016-07-14', '7/15/2016', '7/16/2016', '7/17/2016');
INSERT INTO A VALUES ('F-002200', 0, 750, '2016-07-28', '7/29/2016', '7/30/2016', '7/31/2016');

INSERT INTO B VALUES ('F-001100',  400, '2016-07-14');
INSERT INTO B VALUES ('F-002200',  750, '2016-07-28');
INSERT INTO B VALUES ('F-000767', 1000, '2016-07-28');
INSERT INTO B VALUES ('F-006006',  300, '2016-08-01');
INSERT INTO B VALUES ('F-002200',  900, '2016-07-30');
GO

SELECT B.Item, B.Qty, B.[Date]
from B
WHERE B.Item not in (SELECT item from A where [Status] = 1)

result:

Item    Qty Date
F-002200    750 2016-07-28
F-000767    1000    2016-07-28
F-006006    300 2016-08-01
F-002200    900 2016-07-30