0

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');

2 Answers 2

2

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)
0

SELECT b.id , b.diIndex, b.diMode, b.diStatus FROM box_data b UNION ALL SELECT bl.id , bl.diIndex, bl.diMode, bl.diStatus FROM box_data_logs bl;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.