Problems to select the correct row from a second table

Question

I hope to get some assists from one of you.

My problem is like this: Table one contains some network switch port configuration information. I fill each day all changes into this table to get a history. This table will contain around 12000 lines. This table will grow about 10-20 lines per day. The table has rows like this (tbl_port_config): id, switch_id, port_id, port_name, change_date

The second table (tbl_port_errors) contains error statistics of all ports. This table will be updated every four hours and only ports with errors are inserted. The table looks like this: id, date, switch_id, port_id, error_counter1, error_counter2, ….

So far so easy ;-)

I am looking now for an select statement which gives me all switches and ports of a specific time range. My problem is that I want to add the port_name from tbl_port_config in my query to make the output more user friendly.

Lets say I want to look for the errors on switch 1 port 1 at 2012.03.29 and I want to get the port name from table tbl_port_config and the error counter of that port on the period of time.

But in my table tbl_port_name the most current entry of that switch port has very often a date which is older than the date of the query.

In addition it can happens that not the most current value is correct. Lets assume that we have for a specific switch and port following history of the port name 2012.03.01 name1 2012.03.02 name2 2012.03.08 name8 2012.03.29 name29

Now I want to get the error counter of the date 2012.03.07 for that port. The correct name for the date is name2 which should be included in the out put. Any ideas how I can solve this?

Regards, Andreas

-- phpMyAdmin SQL Dump

-- version 3.4.10.1  

-- http://www.phpmyadmin.net

-- Host: localhost

-- Generation Time: Mar 30, 2012 at 11:04 AM

-- Server version: 5.5.19

-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";  

SET time_zone = "+00:00";

-- Database: test

-- Table structure for table tbl_port_config

CREATE TABLE IF NOT EXISTS tbl_port_config (

  id int(10) unsigned NOT NULL AUTO_INCREMENT,

  switch_id int(10) unsigned NOT NULL,

  port_id int(10) unsigned NOT NULL COMMENT 'decimal port id',

  port_name varchar(32) NOT NULL,

  changedate date NOT NULL COMMENT 'Date when port config has been changed',

  PRIMARY KEY (id)  

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- Dumping data for table tbl_port_config

INSERT INTO tbl_port_config (id, switch_id, port_id, port_name, changedate) VALUES

(1, 2, 0, '---', '2012-01-01'),

(2, 2, 0, 'name1', '2012-01-15'),  

(3, 2, 0, 'name2', '2012-01-19');

-- Table structure for table tbl_port_errors

CREATE TABLE IF NOT EXISTS tbl_port_errors (

  id int(11) NOT NULL AUTO_INCREMENT,

  date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  switch_id int(11) NOT NULL,

  port_id int(11) NOT NULL,

  err_discards_c3 int(11) NOT NULL,

  err_rx_enc_in int(11) NOT NULL,

  err_rx_enc_out int(11) NOT NULL,

  PRIMARY KEY (id),

  KEY date (date),

  KEY switch_id (switch_id),

  KEY port_id (port_id)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Tabelle mit allen error countern' AUTO_INCREMENT=8 ;  

--  

-- Dumping data for table tbl_port_errors

INSERT INTO tbl_port_errors (id, date, switch_id, port_id, err_discards_c3, err_rx_enc_in, err_rx_enc_out) VALUES

(1, '2012-01-13 20:00:00', 2, 0, 150, 151, 152),

(2, '2012-01-13 20:00:00', 2, 1, 151, 151, 151),

(3, '2012-01-13 20:00:00', 2, 2, 152, 152, 152),

(4, '2012-01-13 22:00:00', 2, 0, 220, 220, 220),

(5, '2012-01-13 22:00:00', 2, 2, 222, 222, 222),

(6, '2012-01-20 18:00:00', 2, 0, 180, 180, 180),

(7, '2012-01-22 14:00:00', 2, 0, 140, 140, 140);  

My select statement which I run looks like this:

SELECT p.date, p.switch_id, p.port_id, p.err_discards_c3, c.port_name, c.changedate FROM tbl_port_errors p left join  tbl_port_config c on p.switch_id = c.switch_id and p.port_id = c.port_id WHERE p.err_discards_c3 > 0 

And will return lines like this:

  

date    switch_id   port_id err_discards_c3 port_name   changedate
2012-01-13 20:00:00  2   0   150 --- 2012-01-01
2012-01-13 20:00:00 2   0   150 name1   2012-01-15
2012-01-13 20:00:00 2   0   150 name2   2012-01-19
2012-01-13 22:00:00  2   0   220 --- 2012-01-01
2012-01-13 22:00:00 2   0   220 name1   2012-01-15
2012-01-13 22:00:00 2   0   220 name2   2012-01-19
2012-01-20 18:00:00 2   0   180 --- 2012-01-01
2012-01-20 18:00:00 2   0   180 name1   2012-01-15
2012-01-20 18:00:00  2   0   180 name2   2012-01-19
2012-01-22 14:00:00 2   0   140 --- 2012-01-01
2012-01-22 14:00:00 2   0   140 name1   2012-01-15
2012-01-22 14:00:00  2   0   140 name2   2012-01-19

Only the line in italic are correct for Switch_ID 2 and Port_ID 0.

I want to get only a single row with the correct port name for each line which is in the table tbl_port_errors.
Please have a closer look at the different dates of changedate and date.
I am looking for the next oldest date compared to the date of the selected port error date.
I hope that you can understand my issue. Sorry for the poor formatting but I have to learn a bit more how this forum works...

Regards, Andreas

Answer 1

SELECT tbl_port_config.*, (SELECT TOP 1 error_counter1 FROM tbl_port_errors WHERE switch_id = tbl_port_config.switch_id) as error_counter_1 WHERE ......

Answer 2

MySQL uses LIMIT rather than top. If you want NOT the latest but the second latest, limit it to one row and offset it one row.

SELECT 
    tbl_port_config.*, 
    (SELECT 
        error_counter1 
    FROM 
        tbl_port_errors 
    WHERE 
        switch_id = tbl_port_config.switch_id
    ORDER BY id desc
    LIMIT 1,1) as error_counter_1