1

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

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.

3

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.

  • I'm not sure I understand your point about duplicates. Are you talking about duplicates in B or in A? – Andriy M Jul 14 '16 at 17:05
  • It wasn't clear from question where the 'multiples' lived so I wasn't sure, but meant to indicate that if they exist and all records aren't desired in the result, a ROW_NUMBER() can be used to differentiate between the multiples/duplicates, only relevant if the NOT EXISTS version doesn't produce desired results due to a nuance in the desired handling of the multiples. – Hart CO Jul 14 '16 at 17:11
  • Please see edit about duplicate Items. – JzInqXc9Dg Jul 14 '16 at 17:20
  • I just think that in terms of results, either a join or a NOT EXISTS would work the same (if by the JOIN you mean the LEFT JOIN + IS NULL technique as an equivalent of NOT EXISTS). – Andriy M Jul 14 '16 at 17:27
  • @AndriyM Except if you use a LEFT JOIN and use the ROW_NUMBER() in the join criteria, you can do different things with the multiples, it's a way to arbitrarily match up the multiples, like if you have 3 entries for a item/date pair in table b, and 2 entries for that same item/date pair in table a, then you could use a ROW_NUMBER() to return just 2 of the records from b if so desired, rather than an 'all or nothing' approach. Not clear from question if that would be helpful or not in this case, just mentioning it as it's a useful function in some situations with multiples/duplicates. – Hart CO Jul 14 '16 at 17:34
2

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
0

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.

0

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
  • 1
    Putting brackets around skua doesn't make DISTINCT apply to that column only, in case that was your intention. In T-SQL, DISTINCT always applies to the entire row. And in view of that, DISTINCT is redundant when you have UNION (which means UNION DISTINCT). – Andriy M Jul 14 '16 at 21:27
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.