user6431631 user6431631 - 5 months ago 9
MySQL Question

MySQL join field to result set

I have a result set, and I need to join a related field from another table.

I have the following two tables.
The first table, 'components', keeps information about a component:

+----+-------+------------+-----------+
| id | name | serial_num | model_num |
+----+-------+------------+-----------+
| 1 | comp1 | 0000 | AAAA |
| 2 | comp2 | 0001 | AAAB |
| 3 | comp1 | 0010 | AABA |
| 4 | comp2 | 0011 | AABB |
| 5 | comp3 | 0100 | ABAA |
| 6 | comp1 | 0101 | AAAA |
+----+-------+------------+-----------+


The second table, 'componentLog' tracks which system a component belongs to across time:

+-------------+-----------+---------+---------+-------+
| action_date | component | system | action | notes |
+-------------+-----------+---------+---------+-------+
| 2010-01-01 | 1 | CZMIL01 | added | NULL |
| 2010-02-25 | 1 | CZMIL01 | removed | NULL |
| 2010-01-01 | 2 | CZMIL01 | added | NULL |
| 2010-02-03 | 2 | CZMIL01 | removed | NULL |
| 2010-02-03 | 2 | CZMIL02 | added | NULL |
| 2010-01-14 | 3 | CZMIL02 | added | NULL |
| 2010-01-14 | 4 | CZMIL02 | added | NULL |
| 2010-02-03 | 4 | CZMIL02 | removed | NULL |
| 2010-02-03 | 4 | CZMIL01 | added | NULL |
| 2010-01-14 | 5 | CZMIL02 | added | NULL |
| 2010-02-25 | 6 | CZMIL01 | added | NULL |
+-------------+-----------+---------+---------+-------+


I have a query that tells me which components are in a specified system on a specified date:

SELECT *
FROM components
WHERE id IN (
SELECT component
FROM componentLog
WHERE action_date <= '2010-02-25'
AND system = 'CZMIL01'
)
AND id NOT IN (
SELECT component
FROM componentLog
WHERE action_date <= '2010-02-25'
AND system = 'CZMIL01'
AND action = 'removed'
)
ORDER BY name;


This query provides the following result set:

+----+-------+------------+-----------+
| id | name | serial_num | model_num |
+----+-------+------------+-----------+
| 6 | comp1 | 0101 | AAAA |
| 4 | comp2 | 0011 | AABB |
+----+-------+------------+-----------+


What I need is to join the 'action_date' field from the 'componentLog' table to this result set, thereby specifying when the component was added to the system.

vkp vkp
Answer

Simply join the tables with all the required conditions.

SELECT c.*,cl.action_date
FROM components c
JOIN componentLog cl on c.id = cl.component
WHERE action_date <= '2010-02-25'
AND system = 'CZMIL01'
AND action <> 'removed'
ORDER BY name;

If an component with atleast one removed action for a given system needs to be excluded from the result, use

select t.*, cl.action_date
from (
select * from components c
where not exists (select 1 from componentlog 
                  where component = c.id and action = 'removed' 
                  and system = 'CZMIL01' and action_date <= '2010-02-25')
) t
join componentLog cl on cl.component = t.id 
WHERE system = 'CZMIL01' and action_date <= '2010-02-25'
ORDER BY name;
Comments