Mysql – Show the matching record only for the 3 rows

MySQL

I have 2 tables box_data and box_data_logs, both with same fields (exact same field names and a same number of records), I am trying to match only the following 3 records to see if in those rows in both the tables the values are 101, 201, 301. If the record matches show the matching record

I tried the following buy it throws an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM box_data UNION ALL SELECT box_data_log.id , box_data_log.diIndex, box_data_' at line 2

Here is what I tried 

SELECT box_data.id , box_data.diIndex, box_data.diMode, box_data.diStatus,
FROM box_data
UNION ALL
SELECT box_data_log.id , box_data_log.diIndex, box_data_log.diMode, box_data_log.diStatus,
FROM box_data_log

The table structure with the values

CREATE TABLE IF NOT EXISTS `box_data_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `box_name` varchar(255) DEFAULT NULL,
  `diIndex` tinyint(1) NOT NULL,
  `diMode` tinyint(1) NOT NULL,
  `diStatus` tinyint(1) NOT NULL,
  `status` varchar(125) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;



INSERT INTO `box_data_logs` (`id`, `box_name`, `diIndex`, `diMode`, `diStatus`, `status`, `last_updated`) VALUES
(7, 'boxa', 0, 0, 0, ' ', '2017-03-02 10:49:35'),
(8, 'boxa', 1, 0, 1, ' ', '2017-03-02 10:49:35'),
(9, 'boxa', 2, 0, 0, ' ', '2017-03-02 10:49:36'),
(10, 'boxa', 3, 0, 0, ' ', '2017-03-02 10:49:36'),
(11, 'boxa', 4, 0, 0, ' ', '2017-03-02 10:49:36'),
(12, 'boxa', 5, 0, 0, ' ', '2017-03-02 10:49:36'),
(13, 'boxa', 6, 0, 0, ' ', '2017-03-02 10:49:36'),
(14, 'boxa', 7, 0, 0, ' ', '2017-03-02 10:49:36');

Best Answer

If you're trying to find matching rows in two tables, UNION is not the tool. What you want is INNER JOIN. If you have these two datasets:

mysql> SELECT * FROM box_data;
+----+----------+---------+--------+----------+--------+---------------------+
| id | box_name | diIndex | diMode | diStatus | status | last_updated        |
+----+----------+---------+--------+----------+--------+---------------------+
| 10 | boxa     |       3 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 11 | boxa     |       4 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 12 | boxa     |       5 |      0 |        0 |        | 2017-03-02 10:49:36 |
+----+----------+---------+--------+----------+--------+---------------------+
3 rows in set (0.00 sec)

and:

mysql> SELECT * FROM box_data_logs;
+----+----------+---------+--------+----------+--------+---------------------+
| id | box_name | diIndex | diMode | diStatus | status | last_updated        |
+----+----------+---------+--------+----------+--------+---------------------+
|  7 | boxa     |       0 |      0 |        0 |        | 2017-03-02 10:49:35 |
|  8 | boxa     |       1 |      0 |        1 |        | 2017-03-02 10:49:35 |
|  9 | boxa     |       2 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 10 | boxa     |       3 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 11 | boxa     |       4 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 12 | boxa     |       5 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 13 | boxa     |       6 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 14 | boxa     |       7 |      0 |        0 |        | 2017-03-02 10:49:36 |
+----+----------+---------+--------+----------+--------+---------------------+
8 rows in set (0.00 sec)

and you want to find rows from box_data that have a matching id in box_data_logs, and you want to limit the selected rows to id values of 11 and 12, you would do something like this:

mysql> SELECT box_data.* FROM box_data 
    INNER JOIN box_data_logs 
       ON 
    (box_data.id=box_data_logs.id AND box_data.id IN (11,12));
+----+----------+---------+--------+----------+--------+---------------------+
| id | box_name | diIndex | diMode | diStatus | status | last_updated        |
+----+----------+---------+--------+----------+--------+---------------------+
| 11 | boxa     |       4 |      0 |        0 |        | 2017-03-02 10:49:36 |
| 12 | boxa     |       5 |      0 |        0 |        | 2017-03-02 10:49:36 |
+----+----------+---------+--------+----------+--------+---------------------+
2 rows in set (0.00 sec)
Related Question