I have two tables
table1 - table that defines what sensors are present
serialN | sensor1 | sensor2 |
122 | 1 | 1 |
123 | 1 | 0 |
table 2 - table with actual sensor data.
serialN | sensor1 | sensor2 |
123 | 23 | -0.0F |
123 | 22 | -0.0F |
122 | 19 | -0.0F |
123 | 33 | -0.0F |
122 | 26 | -0.0F |
123 | 24 | -0.0F |
How can I do a PHP mysql Query to get results where only those columns in table 2 are selected ONLY if their corresponding table1 counter part is 1 For a given serialN
So my results if I want data for serialN=123 should be
serialN | sensor1 |
123 | 23 |
123 | 22 |
123 | 33 |
123 | 24 |
Mysql's case statement has your back (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case). PHP trickery is unnecessary:
select
case when t1.sensor1=1 then t2.sensor1 when t1.sensor2=1 then t2.sensor2 end
from table1 t1
inner join table2 t2 on t1.serialN = t2.serialN
See if this fits your needs:
SELECT CASE WHEN t1.sensor1 = 1 THEN t2.sensor1 ELSE null END AS 'SENSOR1',
CASE WHEN t1.sensor2 = 1 THEN t2.sensor2 ELSE null END AS 'SENSOR2'
FROM table2 t2
INNER JOIN table1 T1 ON t1.serialN = t2.serialN