Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got the following two tables:

mysql> DESC domains;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(255) | NO   | UNI | NULL    |                |
| master          | varchar(128) | YES  |     | NULL    |                |
| last_check      | int(11)      | YES  |     | NULL    |                |
| type            | varchar(6)   | NO   |     | NULL    |                |
| notified_serial | int(11)      | YES  |     | NULL    |                |
| account         | varchar(40)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> DESC records;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(11)   | NO   | PRI | NULL    | auto_increment |
| domain_id   | int(11)      | YES  | MUL | NULL    |                |
| name        | varchar(255) | YES  | MUL | NULL    |                |
| type        | varchar(6)   | YES  |     | NULL    |                |
| content     | varchar(455) | YES  |     | NULL    |                |
| ttl         | int(11)      | YES  |     | NULL    |                |
| prio        | int(11)      | YES  |     | NULL    |                |
| change_date | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

I would like to join domains.notified_serial to the query below, id field is common on both tables, could you also please advise if the query below is optimal?

mysql> SELECT * FROM records WHERE name IN ('example.com', 'www.example.com') AND type = 'A';
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
| id         | domain_id | name              | type | content         | ttl  | prio | change_date |
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
| 2926120529 |    620750 | example.com     | A      | 192.168.1.100   |  600 |    0 |  1325776553 |
| 2926120595 |    620750 | www.example.com | A      | 192.168.1.100   |  600 |    0 |  1325776553 |
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
4 rows in set (0.00 sec)

Edit: Wed Jan 29 22:39:41 GMT 2014

mysql> SHOW CREATE TABLE domains\G
*************************** 1. row ***************************
       Table: domains
Create Table: CREATE TABLE `domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `master` varchar(128) DEFAULT NULL,
  `last_check` int(11) DEFAULT NULL,
  `type` varchar(6) NOT NULL,
  `notified_serial` int(11) DEFAULT NULL,
  `account` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=623933 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE records\G
*************************** 1. row ***************************
       Table: records
Create Table: CREATE TABLE `records` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(6) DEFAULT NULL,
  `content` varchar(455) DEFAULT NULL,
  `ttl` int(11) DEFAULT NULL,
  `prio` int(11) DEFAULT NULL,
  `change_date` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rec_name_index` (`name`),
  KEY `nametype_index` (`name`,`type`),
  KEY `domain_id` (`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8544581783 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
share|improve this question
    
can you also post the show create table [table_name] outputs off both tables? –  Raymond Nijland Jan 29 at 21:46
    
Done, basically these are PowerDNS tables. –  HTF Jan 29 at 22:40
add comment

1 Answer 1

up vote 0 down vote accepted

someting like that maybe?

SELECT r.*,d.notified_serial 
FROM records r
left join domains d
    on d.id  = r.domain_id
WHERE name IN ('example.com', 'www.example.com') AND type = 'A';
share|improve this answer
    
- slightly modified: SELECT r.*, d.notified_serial FROM records r LEFT JOIN domains d ON d.id = r.domain_id WHERE r.name IN ('example.com', 'www.example.com') AND r.type = 'A'; –  HTF Jan 30 at 19:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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