I have never posted anything here before so let me start by saying I would have been happy to format it differently but after 10 minutes of trying to “indent by four spaces” per the instructions for code, I gave up as it didn’t seem to work. So, on to my question.

I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2. In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below. How can I accomplish this?

Returns nothing:

SELECT * FROM tbl1,tbl2 WHERE NOT tbl1.package1 != tbl2.package2 

OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT * FROM tbl1,tbl2 WHERE 

(tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

DB SETUP AS FOLLOWS:

CREATE TABLE IF NOT EXISTS `tbl1` (
  `host` varchar(10) NOT NULL,
  `package1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd');

-- Indexes for table `tbl1`
ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

-- Indexes for table `tbl2`
ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Thank you!

I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2. In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below. How can I accomplish this?

Returns nothing:

SELECT *  
FROM tbl1,tbl2  
WHERE NOT tbl1.package1 != tbl2.package2 
OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT *  
FROM tbl1,tbl2  
WHERE (tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

###Database setup

CREATE TABLE IF NOT EXISTS `tbl1` (
  `host` varchar(10) NOT NULL,
  `package1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd');

-- Indexes for table `tbl1`
ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

-- Indexes for table `tbl2`
ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

I have never posted anything here before so let me start by saying I would have been happy to format it differently but after 10 minutes of trying to “indent by four spaces” per the instructions for code, I gave up as it didn’t seem to work. So, on to my question.

I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2. In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below. How can I accomplish this?

Returns nothing:

SELECT * FROM tbl1,tbl2 WHERE NOT tbl1.package1 != tbl2.package2 OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT * FROM tbl1,tbl2 WHERE (tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

DB SETUP AS FOLLOWS:

CREATE TABLE IF NOT EXISTS tbl1 ( host varchar(10) NOT NULL, package1 varchar(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS tbl2 ( packageid tinyint(10) NOT NULL, package2 varchar(10) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO tbl2 (packageid, package2) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

-- Indexes for table tbl1 ALTER TABLE tbl1 ADD PRIMARY KEY (host,package1) USING BTREE;

-- Indexes for table tbl2 ALTER TABLE tbl2 ADD PRIMARY KEY (packageid);

ALTER TABLE tbl2 MODIFY packageid tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Thank you!

I have never posted anything here before so let me start by saying I would have been happy to format it differently but after 10 minutes of trying to “indent by four spaces” per the instructions for code, I gave up as it didn’t seem to work. So, on to my question.

I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2. In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below. How can I accomplish this?

Returns nothing:

SELECT * FROM tbl1,tbl2 WHERE NOT tbl1.package1 != tbl2.package2 

OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT * FROM tbl1,tbl2 WHERE 

(tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

DB SETUP AS FOLLOWS:

CREATE TABLE IF NOT EXISTS `tbl1` (
  `host` varchar(10) NOT NULL,
  `package1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd');

-- Indexes for table `tbl1`
ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

-- Indexes for table `tbl2`
ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Thank you!