I've a table A with columns COL_1 and COL_2

• COL_1 contains an id for a deck of cards (52).
• COL_2 contains the combination, but without itself

How can I check if a combination like 3 & 4 is not present, automatically?

For example, for 4 cards and relation 3=4 is missing here:

  CREATE TABLE deck(
  id int,
  col_1 int,
  col_2 int
);

INSERT INTO deck VALUES
(1, 1, 2),
(2, 1, 3),
(3, 1, 4),
(4, 2, 1),
(5, 2, 3),
(6, 2, 4),
(7, 3, 1),
(8, 3, 2),
(9, 4, 1),
(10, 4, 2),
(11, 4, 3)

I do this:

SELECT * FROM desk WHERE col_2 NOT IN (
SELECT col_1 FROM deck GROUP BY col_1)

but I think this should be on every col_1 item.

CodePudding user response：

There's a specific approach for doing this, which is finding the:

Cartesian product of all possible combinations, with an anti-join to eliminate combinations that do exist, leaving combinations that do not exist.

with an additional condition in the WHERE clause to only display specific missing values that you need using WHERE a.col_1 != a.col_2; this will scale your entire deck.

See more from this answer here.

Then you can optionally perform a UNION to add the missing rows to your current table's result set.

I've also added a Y/N flag to indicate the missing row.

SELECT id, 
       col_1, 
       col_2, 
       'N' AS missing_value 
FROM deck
UNION
SELECT a.id,
       a.col_1,
       a.col_2,
       'Y' AS missing_value
FROM 
(
  SELECT b.id, b.col_1, c.col_2 FROM 
    (SELECT DISTINCT id, col_1 FROM deck) AS b 
    CROSS JOIN 
    (SELECT DISTINCT id, col_2 FROM deck) AS c 
) AS a 
LEFT JOIN deck AS d 
  ON d.col_1 = a.col_1 AND 
     d.col_2 = a.col_2 
WHERE d.col_1 IS NULL AND 
      d.col_2 IS NULL 
      AND a.col_1 != a.col_2 

Result:

id	col_1	col_2	missing_value
1	1	2	N
1	1	3	N
1	1	4	N
1	2	1	N
1	2	3	N
1	2	4	N
1	3	1	N
1	3	2	N
1	3	4	Y
1	4	1	N
1	4	2	N
1	4	3	N

db<>fiddle here.

CodePudding user response：

As you are searching all number, it would be best to use CTEs to generate a number column, with all number(assuming that MAX(col_1) will give that

The rest is simple logic, but it will find also if in col_1 are missing numbers and find them

But with bigger numbers this could get a little time consuming

  CREATE TABLE deck(
  id int,
  col_1 int,
  col_2 int
);

INSERT INTO deck VALUES
(1, 1, 2),
(2, 1, 3),
(3, 1, 4),
(4, 2, 1),
(5, 2, 3),
(6, 2, 4),
(7, 3, 1),
(8, 3, 2),
(9, 4, 1),
(10, 4, 2),
(11, 4, 3)

11 rows affected

DECLARE @end INTEGER ;
SET @end = (SELECT MAX(col_1) FROM deck);  
WITH Nums AS
(
  SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects 

),
 CTEnum as (
SELECT n FROM Nums 
WHERE n BETWEEN 1 AND @end
)
SELECT  L1.n,L2.n  FROM (CTEnum L1 CROSS JOIN CTEnum L2) 
  LEFT JOIN deck ON l2.n = Deck.col_2 and l1.n = Deck.col_1
WHERE Deck.id IS NULL

n	n
1	1
2	2
3	3
3	4
4	4

fiddle