Mysql – Pick one single row each time from either table

join;MySQL

I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime depends on which time comes first. 

CREATE TABLE IF NOT EXISTS `tblGAlert` (
  `gAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `gEntryStatus` enum('Do','Ad','Ej') NOT NULL,
  `gDateTime` datetime NOT NULL,
  `gInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `gMessage` varchar(255) NOT NULL,
   PRIMARY KEY (`gAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tblEAlert` (
  `eAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `eDateTime` datetime NOT NULL,
  `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eMessage` varchar(255) NOT NULL,
  PRIMARY KEY (`eAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sample data I say for tblGAlert

1,12,1122,'Do',2013-07-13 14:30:19,2013-07-13 15:30:19,''
2,13,1122,'Ad',2013-07-13 14:35:19,2013-07-13 15:35:19,''
3,13,1122,'Ad',2013-07-13 14:38:19,2013-07-13 15:39:19,''
4,14,1122,'Ej',2013-07-13 14:45:19,2013-07-13 15:55:19,''
5,14,1122,'Ej',2013-07-13 14:50:19,2013-07-13 15:56:19,''

Same data for tblEAlert.

1,1,1122,2013-07-13 14:33:19,2013-07-13 15:35:19,''
2,5,1122,2013-07-13 14:36:19,2013-07-13 15:36:19,''
3,6,1122,2013-07-13 14:37:19,2013-07-13 15:39:19,''
4,7,1122,2013-07-13 14:48:19,2013-07-13 15:55:19,''
5,8,1122,2013-07-13 14:52:19,2013-07-13 15:56:19,''

The output I want to merge them together in such a way arrange by the gDateTime and eDateTime. Any idea how to merge them ?

I have tried below script but the answer I get is not right and it hogs my db server.

SELECT X . * 
FROM (

SELECT gAlertID,  'gType' AS PTYPE, gDateTime AS DATE
FROM tblGAlert
WHERE tblGAlert.aID =2494
UNION 
SELECT eAlertID,  'eType' AS PTYPE, eDateTime AS DATE
FROM tblEAlert
WHERE tblEAlert.aID =2494
)X
ORDER BY X.`date`

Best Answer

First of all, this is my first post on dba.stackexchange.com, so I apologize in advance if there is any etiquette that I fail to follow, and am open to your input as to how I can improve future posts.

Best database design practices would call for consolidating the two very similar tables into just one table, like sa555 described. The issue you are currently facing is the direct consequence of having these two tables separate.

However, since you are working with a live system, I would recommend asking yourself and/or the rest of your development team (if applicable) the following question:

  • Will the future benefits of consolidating these two tables outweigh the time and effort put into the process of consolidating the two tables? Keep in mind that any applications depending on these two tables being separate will need to be updated also.

If the benefits outweigh the costs, I would recommend consolidating the two tables, just as sa555 recommended. If not, I have included the MySQL code below to create a stored procedure that will return the most recent row from either table. Just remember that this is a mere surface-level fix, and that your database still contains a design flaw that could impede future development.

Here are some important notes on the procedure's edge-case behavior:

  • If both tables are empty, the procedure will not return a result set at all.
  • If only one of the tables contains records, the most recent record from the non-empty table will be returned.
  • If the most recent records from both tables have the exact same date and time, the record from tblGAlert will be returned. You can return the tblEAlert record by changing the inequality from ">=" to ">". (comments will explain where to do this)
  • Since both tables have different column names, you may want to canonicalize the column names in this procedure's result set using column name aliases (e.g. SELECT eDateTime AS AlertDateTime). The comments I have inserted into the code will direct you as to where to insert the aliases.

SQL Query (creates stored procedure):

DELIMITER $$
CREATE PROCEDURE GetLatestAlert()
BEGIN
-- Select latest gAlert record
SELECT gDateTime, gAlertID INTO @gAlertDateTime, @gID FROM tblGAlert ORDER BY gDateTime DESC LIMIT 1;

-- Select latest eAlert record
SELECT eDateTime, eAlertID INTO @eAlertDateTime, @eID FROM tblEAlert ORDER BY eDateTime DESC LIMIT 1;

-- We still want to be able to return a record in the event
-- that one of the tables does not contain a record, so we make
-- the default @gAlertDateTime and @eAlertDateTime values equal to the earliest
-- MySQL date possible, instead of the default value of null (if
-- no records were returned from that respective table). This is
-- because the NULL value cannot be compared using the ">" operator
-- that appears later on in this procedure.
IF @gAlertDateTime IS NULL THEN
    SET @gAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

IF @eAlertDateTime IS NULL THEN
    SET @eAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

-- Before comparing the dates, make sure one or both of the tables contain(s) a record.
IF @gID IS NOT NULL OR @eID IS NOT NULL THEN

  -- Compare the dates and return the latest row.
  -- Note: if one of the tables does not contain a record,
  -- the default values for @gAlertDateTime and @eAlertDateTime are the earliest
  -- possible date in MySQL, which will automatically make the
  -- logic choose the table that contains the record.
  IF @gAlertDateTime >= @eAlertDateTime THEN 
     -- Change the inequality from ">=" to ">" in order to return the tblEAlert record
     -- in the event that both records have the same date and time.

     -- NOTE: You may want to canonicalize the column names for the tblGAlert and 
     -- tblEAlert results by adding column aliases. This way the application using
     -- the data will get consistent column names.
     SELECT * FROM tblGAlert WHERE gAlertID = @gID;
  ELSE
     -- If you canonicalize the column names, insert the aliases here as well.
     SELECT * FROM tblEAlert WHERE eAlertID = @eID;
  END IF;

END IF;

-- If no records were returned from either table, this procedure
-- will not return a resultset at all.
END; $$

Now just execute CALL GetLatestAlert(); and the most recent row from either table will be returned.

Related Question