Query on two tables for one report (Advanced)

Question

I'm having some trouble with an advanced SQL query, and it's been a long time since I've worked with SQL databases. We use MySQL.

Background:

We will be working with two tables:

"Transactions Table"

table: expire_history

+---------------+-----------------------------+------+-----+-------------------+-------+    
| Field         | Type                        | Null | Key | Default           | Extra |
+---------------+-----------------------------+------+-----+-------------------+-------+
| m_id          | int(11)                     | NO   | PRI | 0                 |       | 
| m_a_ordinal   | int(11)                     | NO   | PRI | 0                 |       | 
| a_expired_date| datetime                    | NO   | PRI |                   |       | 
| a_state       | enum('EXPIRED','UNEXPIRED') | YES  |     | NULL              |       | 
| t_note        | text                        | YES  |     | NULL              |       | 
| t_updated_by  | varchar(40)                 | NO   |     |                   |       | 
| t_last_update | timestamp                   | NO   |     | CURRENT_TIMESTAMP |       | 
+---------------+-----------------------------+------+-----+-------------------+-------+

"Information Table"

table: information

+---------------------+---------------+------+-----+---------------------+-------+
| Field               | Type          | Null | Key | Default             | Extra |
+---------------------+---------------+------+-----+---------------------+-------+
| m_id                | int(11)       | NO   | PRI | 0                   |       | 
| m_a_ordinal         | int(11)       | NO   | PRI | 0                   |       | 
| a_type              | varchar(15)   | YES  | MUL | NULL                |       | 
| a_class             | varchar(15)   | YES  | MUL | NULL                |       | 
| a_state             | varchar(15)   | YES  | MUL | NULL                |       | 
| a_publish_date      | datetime      | YES  |     | NULL                |       | 
| a_expire_date       | date          | YES  |     | NULL                |       | 
| a_updated_by        | varchar(20)   | NO   |     |                     |       | 
| a_last_update       | timestamp     | NO   |     | CURRENT_TIMESTAMP   |       | 
+---------------------+---------------+------+-----+---------------------+-------+

We have a set of fields in one table that describe the record. Each record is comprised of a m_id (the person) and an ordinal (a person can have multiple records). So for instance, my m_id could be 1, and i could have multiple ordinals, (1, 2, 3, 4, etc), each with their own individual set of data. The m_id and the m_a_ordinal comprise a composite key in the "information" table, and the m_id, m_a_ordinal, and a_expired_date fields in the "transactions" table comprises a composite key as well.

Essentially when we expire a record, the a_state field in the information table is updated to expired. At the same time, a record is created in the transactions table with the m_id, m_a_ordinal, and a_expired_date. We've found in the past that people get impatient and can click a button twice, so through some previous help I've managed to narrow down the most recent transaction for each expired record using the following query:

SELECT e1.m_id, e1.m_a_ordinal, e1.a_expired_date, e1.t_note, e1.t_updated_by 
FROM expire_history e1 
    INNER JOIN  (SELECT m_id, m_a_ordinal, MAX(a_expired_date) AS a_expired_date 
    FROM expire_history GROUP BY m_id, m_a_ordinal) e2 
    ON (e2.m_id = e1.m_id AND e2.m_a_ordinal = e1.m_a_ordinal AND e2.a_expired_date = e1.a_expired_date) 
WHERE e2.a_expired_date > '2008-05-15 00:00:00' ORDER BY a_date_expired;

Seems simple enough, right?

Let's add some complexity. Each record in the "information" table has a "natural expiration date" as well. The original developer of our software, however, didn't code it to change the state of the record to "expired" once it's reached it's natural expiration date. It also does not write a transaction to the transaction table once it's expired (which I understand because this is only to keep records of ones that were expired by a person, as opposed to automagically). Also, when a record is expired manually, the original expiration date does not change. This is why this is so complicated :P~~.

Essentially I need to build a report that shows all aspects of expiration, whether it was expired manually, or naturally.

This report should take the data from the query above, and combines it with another query on the "information table" that says if a_expire_date <= CURDATE show record, except if record exisits in (query above from expire_history), then show record from (query on expire_history).

a rough structure of the raw logic is as follows:

for x in record_total
   if (m_id m_a_ordinal) exists in expire_history
      display m_id, m_a_ordinal, a_expired_date, a_state)
   else if (m_id_a_ordinal) exists in information AND a_expire_date <= CURDATE
      display (m_id, m_a_ordinal, a_expire_date, a_state)
   end if
x++

I hope that this is concise enough.

Thanks for any help you can provide!

Answer 1

SELECT i.m_id, I.m_a_ordinal, 
   coalesce(e1.a_expired_date, I.A_Expire_Date) as Expire_DT, 
   coalesce(e1.t_note,'insert related item column'),
   coalesce(e1.t_updated_by, I.A_Updated_by) as Updated_By
FROM Information I 
    LEFT JOIN expire_history e1 
      ON E1.M_ID = I.M_ID
      AND I.m_a_ordinal=e1.M_a_ordinal
    INNER JOIN  
       (SELECT m_id, m_a_ordinal, MAX(a_expired_date) AS a_expired_date 
        FROM expire_history GROUP BY m_id, m_a_ordinal) e2 
      ON (e2.m_id = e1.m_id 
      AND e2.m_a_ordinal = e1.m_a_ordinal 
      AND e2.a_expired_date = e1.a_expired_date) 
WHERE coalesce(e2.a_expired_date,i.A_Expire_Date) > '2008-05-15 00:00:00' 
ORDER BY a_date_expired;

Syntax may be off a bit don't ahve time to test; but you can get the gist of it from this I hope:

Again what coalesce does is simply return the first NON-null value in a series of values. If you're only dealing with two NULLIF may work as well.