将数据从一个表复制到另一个表，但添加第二个WHERE子句不会插入数据

Question

I have two tables. When A user clicks a button, it will copy data from table 1 to table 2 where the doesn't already exist in table 2.

I had the query working just fine, until I added a new column in the first table named "onsite" - which is either set to yes, or it is NULL.

Here is the query I've tried. It no longer inserts ANY data to the past_bidder table (table 2)

$copybidderquery = "INSERT INTO past_bidders(bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created) 
SELECT bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created 
FROM bidders 
WHERE (bidfname, bidlname, bidphnum, bidlicense, bidaddress) 
NOT IN (SELECT bidfname, bidlname, bidphnum, bidlicense, bidaddress FROM past_bidders) 
AND onsite != 'yes'";

I've also tried moving the where onsite != 'yes' to right after "WHERE" and it still does the same thing.

$copybidderquery = "INSERT INTO past_bidders(bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created) 
SELECT bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created 
FROM bidders 
WHERE onsite != 'yes'
AND (bidfname, bidlname, bidphnum, bidlicense, bidaddress) 
NOT IN (SELECT bidfname, bidlname, bidphnum, bidlicense, bidaddress FROM past_bidders)";

As stated, this query works just fine without the "onsite != 'yes'" line.

I think it's just some little syntax error I'm overlooking. Any help would be appreciated.

Answer 1

INSERT INTO past_bidders(bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created) 
SELECT bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created 
FROM bidders 
WHERE  onsite is NULL AND (bidfname, bidlname, bidphnum, bidlicense, bidaddress) 
NOT IN (SELECT bidfname, bidlname, bidphnum, bidlicense, bidaddress FROM past_bidders) 
;

Check following example when you say not equal to something it will not return the null values

mysql> select * from calls;
+----+------------+---------+
| id | date       | user_id |
+----+------------+---------+
|  1 | 2016-06-22 |       1 |
|  2 | 2016-06-22 |    NULL |
|  3 | 2016-06-22 |    NULL |
|  4 | 2016-06-23 |       2 |
|  5 | 2016-06-23 |       1 |
|  6 | 2016-06-23 |       1 |
|  7 | 2016-06-23 |    NULL |
+----+------------+---------+
7 rows in set (0.00 sec)

mysql> select * from calls where user_id!=1;
+----+------------+---------+
| id | date       | user_id |
+----+------------+---------+
|  4 | 2016-06-23 |       2 |
+----+------------+---------+
1 row in set (0.00 sec)

mysql> select * from calls where user_id is null;
+----+------------+---------+
| id | date       | user_id |
+----+------------+---------+
|  2 | 2016-06-22 |    NULL |
|  3 | 2016-06-22 |    NULL |
|  7 | 2016-06-23 |    NULL |
+----+------------+---------+
3 rows in set (0.00 sec)