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 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`
share|improve this question
    
Can you provide sample data for your tables and desired output for the query? –  peterm Aug 29 '13 at 18:18
    
What do you mean with "the answer I get is not right"? –  ypercube Aug 30 '13 at 23:23
    
What I mean is the query results is wrong here. –  biz14 Aug 31 '13 at 3:58
add comment

4 Answers

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.

share|improve this answer
    
Welcome to DBA.SE! Thanks for taking the time to share all of this great detail; I hope it's useful for the OP. –  Jon of All Trades Aug 30 '13 at 21:22
    
@data-dan yes look like we taking into consideration of dumping everything into one table but I dont know how to handle the existing data and system which fully rely on this structure for now any idea what to do with the current data ? –  biz14 Aug 31 '13 at 3:58
    
@data-dan actually I already have this in mind to manually compare the results of both query and pick either else if both have not data then just send with no data. But this might eat up some time compare to just a single table. –  biz14 Aug 31 '13 at 4:05
    
@biz14 Yes, the stored procedure above will be slower than running a single SQL SELECT statement on the consolidated table (the stored procedure runs three SELECT queries plus some logic). However, the stored procedure should not be that intensive since all the SQL queries it uses are very basic. In any case, if you end up using this stored procedure (or another solution on this page), I would recommend running some performance tests to make sure it will perform sufficiently for both current and future anticipated loads. –  data-dan Aug 31 '13 at 19:32
    
@data-dan yes I am still reviewing what is best but I might be changing to a single table though. Even if I use your method I dont think it will be lots of delay cause they are simple queries and I am gonna limit results just to 1. Anything else you can see I must improve on my tables I am going to post another question with regards these too. –  biz14 Sep 1 '13 at 3:15
add comment

Your question doesn't provide enough details to be 100% percent sure but a general type of query for your problem might look like this

SELECT a.aid, 
       IF(COALESCE(g.gDateTime, 0) >= COALESCE(e.eDateTime, 0), g.gAlertID, e.eAlertID) alertid,
       IF(COALESCE(g.gDateTime, 0) >= COALESCE(e.eDateTime, 0), 'gType', 'eType') ptype,
       IF(COALESCE(g.gDateTime, 0) >= COALESCE(e.eDateTime, 0), g.gDateTime, e.eDateTime) datetime
  FROM
(
    SELECT aid FROM tblGAlert
    UNION
    SELECT aid FROM tblEAlert
) a LEFT JOIN
(
    SELECT t.*
      FROM tblgalert t JOIN
    (
      SELECT aid, MAX(galertid) alertid
        FROM tblgalert
       GROUP BY aid 
    ) m ON t.galertid = m.alertid
) g ON a.aid = g.aid LEFT JOIN
(   
    SELECT t.*
      FROM tblealert t JOIN
    (
      SELECT aid, MAX(ealertid) alertid
        FROM tblealert
       GROUP BY aid 
    ) m ON t.ealertid = m.alertid
) e ON a.aid = e.aid

Note:

  • assumption is that the bigger xAlertID the more recent time and that is used in subqueries to grab the latest row per aid. If for some reason it's not the case then it can be rewritten to get MAX(xDateTime) and then JOIN back to grab other columns.
  • first subquery builds a list of aid from both tables it can be changed to the subquery you mentioned in your question
  • assumption is that not necessarily rows exist in both table for a given aid. That's why LEFT JOINs are used
  • if there are lots of data in both tables, then you'll need to filter rows in both subqueries g and e with WHERE aid IN(...) to prevent processing the whole tables

Output:

|  AID | ALERTID | PTYPE |                    DATETIME |
|------|---------|-------|-----------------------------|
| 1122 |       5 | eType | July, 13 2013 14:52:19+0000 |

Here is SQLFiddle demo


UPDATE: Another approach involves ranking

SELECT aid, alertid, ptype, datetime
   FROM
(
  SELECT aid, alertid, ptype, datetime, 
         @n := IF(@g = aid, @n + 1, 1) rnum,
         @g := aid
    FROM
  (
    SELECT aid, gAlertID alertid, 'gType' ptype, gDateTime datetime
      FROM tblgalert
     WHERE aid IN(1122, 1123)
    UNION ALL
    SELECT aid, eAlertID alertid, 'eType' ptype, eDateTime datetime
      FROM tblealert
     WHERE aid IN(1122, 1123)
  ) q CROSS JOIN (SELECT @n := 0) i
   ORDER BY aid, datetime DESC
) p
 WHERE rnum = 1

Sample output:

|  AID | ALERTID | PTYPE |                    DATETIME |
|------|---------|-------|-----------------------------|
| 1122 |       5 | eType | July, 13 2013 14:52:19+0000 |
| 1123 |       9 | gType | July, 13 2013 11:39:00+0000 |

Here is SQLFiddle demo

share|improve this answer
    
I am not too sure with colalese and read a google which say the very first data. In these case are you comparing among them is it ? –  biz14 Aug 31 '13 at 4:03
    
@biz14 COALESCE in my example is used to make sure that if one of your columns gDateTime or gDateTime is NULL meaning theres is no rows in that table for the aId then it will be replaced with a zero date to make sure that compare will work. –  peterm Aug 31 '13 at 4:07
    
I understand the idea but the syntax why are there 3 lines of COALESCE and I notice you are compare the data time among them first right ? –  biz14 Aug 31 '13 at 15:52
    
It's because first latest (with max ids) records are retrieved from both tables then joined and then in the outer select we decide witch fields to grab based on your condition. –  peterm Aug 31 '13 at 16:08
    
@biz14 See updated answer for one more solution –  peterm Aug 31 '13 at 18:35
show 14 more comments

Could you have this instead?

Where alertTypeID determines if it's e or g?

Because the rest of the columns are the same you could simplify the database design.

CREATE TABLE IF NOT EXISTS `tblAlert` (
  `alertID` int(11) NOT NULL AUTO_INCREMENT,
  `alertTypeID` int(11) NOT NULL,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `entryStatus` enum('Do','Ad','Ej') NOT NULL,
  `dateTime` datetime NOT NULL,
  `insertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Message` varchar(255) NOT NULL,
  PRIMARY KEY (`gAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
share|improve this answer
    
is there any other mechnanism for this ? Cause it is a live system running now. Thus will be kind of difficult to change at this present time –  biz14 Aug 30 '13 at 16:56
add comment

I think that the query is slow for 2 reasons:

  • There is no index on aID, in either of the two tables so no index is used and both tables are scanned in every run of the query.
  • You use UNION which is equivalent to UNION DISTINCT, while the two subqueries cannot have duplicate rows.

So, my advice to counter the inefficiency is to:

  • add indexes on (aID, gDateTime, gAlertID) and (aID, eDateTime, eAlertID) respectively, which are covering indexes for the query.
  • use UNION ALL.
  • you could also simplify the query by removing the derived table (that is probably irrelevant to efficiency):

        SELECT gAlertID AS alertID, 'gType' AS ptype, gDateTime AS date
        FROM tblGAlert
        WHERE aID = 2494
    UNION ALL
        SELECT eAlertID,            'eType',          eDateTime
        FROM tblEAlert
        WHERE aID = 2494
    ORDER BY date ;
    

If you want a single row (with oldest date), you can use:

      ( SELECT gAlertID AS alertID, 'gType' AS ptype, gDateTime AS date
        FROM tblGAlert
        WHERE aID = 2494
        ORDER BY date
        LIMIT 1
      ) 
    UNION ALL
      ( SELECT eAlertID,            'eType',          eDateTime AS date
        FROM tblEAlert
        WHERE aID = 2494
        ORDER BY date
        LIMIT 1
      )
    ORDER BY date 
    LIMIT 1 ;
share|improve this answer
    
should I add index separate or index all 3 fields together? –  biz14 Aug 31 '13 at 3:56
    
One index with the 3 columns in that order. –  ypercube Aug 31 '13 at 12:16
    
ypercube sorry I am trying to master index normally we look into the usage of where right ? So in this case the where is jsut looking for aID so why do I need to index the rest? –  biz14 Aug 31 '13 at 15:50
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.