Redshift Join on multiple conditions but return only where one condition doesn't match-CodePudding

I have two tables, the first has ID's and Numbers. The ID's and numbers can potentially be listed more than once.

My second table has rows (100 million) with the ID and Numbers again (as well as other data.) I need to search that table for any ID that has a Number not in the list of Numbers from the result table.

I am having trouble with a join that returns any records from table 2 that match the first qualifier, but not the second.

DROP TABLE IF EXISTS `myTable`;

CREATE TABLE `myTable` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `ID` varchar(255),
  `Numbers` mediumint default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `myTable` (`ID`,`Numbers`)
VALUES
  ("CRQ44MPX1SZ",1890),
  ("UHO21QQY3TW",4370),
  ("JTQ62CBP6ER",1825),
  ("RFD95MLC2MI",5014),
  ("URZ04HGG2YQ",2859),
 ("CRQ44MPX1SZ",1891),
  ("UHO21QQY3TW",4371),
  ("JTQ62CBP6ER",1826),
  ("RFD95MLC2MI",5015),
  ("URZ04HGG2YQ",2860),
 ("CRQ44MPX1SZ",1892),
  ("UHO21QQY3TW",4372),
  ("JTQ62CBP6ER",1827),
  ("RFD95MLC2MI",5016),
  ("URZ04HGG2YQ",2861);



DROP TABLE IF EXISTS `myTable2`;

CREATE TABLE `myTable2` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `ID` varchar(255),
  `Numbers` mediumint default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `myTable2` (`ID`,`Numbers`)
VALUES
  ("CRQ44MPX1SZ",1870),
  ("UHO21QQY3TW",4350),
  ("JTQ62CBP6ER",1825),
  ("RFD95MLC2MI",5014),
  ("URZ04HGG2YQ",2859),
 ("CRQ44MPX1SZ",1891),
  ("UHO21QQY3TW",4371),
  ("JTQ62CBP6ER",1826),
  ("RFD95MLC2MI",5015),
  ("URZ04HGG2YQ",2860),
 ("CRQ44MPX1SZ",1882),
  ("UHO21QQY3TW",4372),
  ("JTQ62CBP6ER",1827),
  ("RFD95MLC2MI",5016),
  ("URZ04HGG2YQ",2861);

SELECT mytable1.ID, listagg(distinct mytable2.Numbers, ',') as unauth_list, count(mytable2.Numbers) as unauth_count
FROM mytable1
LEFT JOIN mytable2 on mytable1.id = mytable2.id
WHERE (mytable1.id = mytable2.id)
AND (mytable1.Numbers <> mytable2.Numbers)
GROUP BY mytable1.id

Expected output:

(“CRQ44MPX1SZ”, ”1870,1882”, 2)
(“UHO21QQY3TW”, ”4350”, 1)

CodePudding user response：

Steve. You are close. You need to reverse your tables in the FROM clause (or use RIGHT JOIN) and add a JOIN ON clause. The WHERE clause will now be bases on seeing NULL in Numbers for table1. With your data setup try this:

SELECT mytable2.ID, listagg(distinct mytable2.Numbers::text, ',') as unauth_list, count(mytable2.Numbers) as unauth_count
FROM mytable2
LEFT JOIN mytable1 on mytable1.id = mytable2.id 
and mytable1.numbers = mytable2.numbers
Where mytable1.Numbers is null
GROUP BY mytable2.id;

See fiddle - http://sqlfiddle.com/#!15/44b5e/4

Produces results:

id  unauth_list unauth_count
CRQ44MPX1SZ 1870,1882   2
UHO21QQY3TW 4350    1

SF wants me to repeat your data set up so

CREATE TABLE myTable1 (
  ID varchar(255),
  Numbers int default NULL
) ;

INSERT INTO myTable1 (ID,Numbers)
VALUES
  ('CRQ44MPX1SZ',1890),
  ('UHO21QQY3TW',4370),
  ('JTQ62CBP6ER',1825),
  ('RFD95MLC2MI',5014),
  ('URZ04HGG2YQ',2859),
 ('CRQ44MPX1SZ',1891),
  ('UHO21QQY3TW',4371),
  ('JTQ62CBP6ER',1826),
  ('RFD95MLC2MI',5015),
  ('URZ04HGG2YQ',2860),
 ('CRQ44MPX1SZ',1892),
  ('UHO21QQY3TW',4372),
  ('JTQ62CBP6ER',1827),
  ('RFD95MLC2MI',5016),
  ('URZ04HGG2YQ',2861);


CREATE TABLE myTable2 (
  ID varchar(255),
  Numbers int default NULL
) ;

INSERT INTO myTable2 (ID,Numbers)
VALUES
  ('CRQ44MPX1SZ',1870),
  ('UHO21QQY3TW',4350),
  ('JTQ62CBP6ER',1825),
  ('RFD95MLC2MI',5014),
  ('URZ04HGG2YQ',2859),
 ('CRQ44MPX1SZ',1891),
  ('UHO21QQY3TW',4371),
  ('JTQ62CBP6ER',1826),
  ('RFD95MLC2MI',5015),
  ('URZ04HGG2YQ',2860),
 ('CRQ44MPX1SZ',1882),
  ('UHO21QQY3TW',4372),
  ('JTQ62CBP6ER',1827),
  ('RFD95MLC2MI',5016),
  ('URZ04HGG2YQ',2861);