Database query : sequence as step parent

[CyGa]

Hi all,

I'm building very simple sequences that will all follow this architecture :

 * MainSequence Callback

      * SpecSequence_1

          * TestSequence_1

               * Step_DoThis

               * Step_DoThat

               * ...

          *TestSequence_2

               * Step_DoItBetter

               *....

      * SpecSequence_2

           *TestSequence_3

...

I'm using standard TS database schema to get all result in a MySQL database. And I need to create a request which will return an answer like this :

SpecSequence | TestSequence | Step_Name | DataSource | CompOperator |High Limits | Low Limts| Units | Status

This request allows to get almost what I want for a specific UUT ID:

SELECT
`step_seqcall`.`SEQUENCE_NAME` AS `SEQUENCE_NAME`,
`step_result`.`STEP_NAME` AS `STEP_NAME`,
`prop_result`.`DATA` AS `DATA`,
`prop_numericlimit`.`COMP_OPERATOR` AS `COMP_OPERATOR`,
`prop_numericlimit`.`HIGH_LIMIT` AS `HIGH_LIMIT`,
`prop_numericlimit`.`LOW_LIMIT` AS `LOW_LIMIT`,
`prop_numericlimit`.`UNITS` AS `UNITS`,
`prop_numericlimit`.`STATUS` AS `STATUS`

FROM

((((`prop_numericlimit`
JOIN `prop_result` ON ((`prop_numericlimit`.`PROP_RESULT` = `prop_result`.`ID`)))
JOIN `step_result` ON ((`prop_result`.`STEP_RESULT` = `booster_results`.`step_result`.`ID`)))
JOIN `uut_result` ON ((`step_result`.`UUT_RESULT` = `uut_result`.`ID`)))
JOIN `step_seqcall` ON ((`step_seqcall`.`STEP_RESULT` = `step_result`.`STEP_PARENT`)))

WHERE
(`uut_result`.`ID` = '{20537569-ab6c-11e3-a662-f4b7e2f8d8e9}');

The problem is that I can't find a way to add my SpecSequenceName as the leftmost column!

So I only get an answer like this :

TestSequence | Step_Name | DataSource | CompOperator |High Limits | Low Limts| Units | Status

Maybe some of you are SQL masters and let me know I can get the answer I want from a single request ?

Thanks !

[CyGa · Accepted solution]

For those who are interested, I found a solution :

SELECT
`MyDB`.`step_seqcall`.`SEQUENCE_NAME` AS `TEST_SEQUENCE_NAME`,
(
SELECT `MyDB`.`step_seqcall`.`SEQUENCE_NAME`
FROM `MyDB`.`step_seqcall`
INNER JOIN `MyDB`.`step_result` ON `MyDB`.`step_result`.`STEP_PARENT` = `MyDB`.`step_seqcall`.`STEP_RESULT`
INNER JOIN `MyDB`.`uut_result` ON `MyDB`.`uut_result`.`ID` = `MyDB`.`step_result`.`UUT_RESULT`
WHERE `MyDB`.`step_result`.`STEP_NAME` = `TEST_SEQUENCE_NAME` AND `MyDB`.`uut_result`.`ID` = '{f241ea4c-b8da-11e3-a4e2-005056c00008}'
) AS `SPEC_SEQUENCE_NAME`,
`MyDB`.`step_result`.`STEP_NAME` AS `STEP_NAME`,
`MyDB`.`prop_result`.`DATA` AS `DATA`,
`MyDB`.`prop_numericlimit`.`COMP_OPERATOR` AS `COMP_OPERATOR`,
`MyDB`.`prop_numericlimit`.`HIGH_LIMIT` AS `HIGH_LIMIT`,
`MyDB`.`prop_numericlimit`.`LOW_LIMIT` AS `LOW_LIMIT`,
`MyDB`.`prop_numericlimit`.`UNITS` AS `UNITS`,
`MyDB`.`prop_numericlimit`.`STATUS` AS `STATUS`
FROM
((((`MyDB`.`prop_numericlimit`
JOIN `MyDB`.`prop_result` ON ((`MyDB`.`prop_numericlimit`.`PROP_RESULT` = `MyDB`.`prop_result`.`ID`)))
JOIN `MyDB`.`step_result` ON ((`MyDB`.`prop_result`.`STEP_RESULT` = `MyDB`.`step_result`.`ID`)))
JOIN `MyDB`.`uut_result` ON ((`MyDB`.`step_result`.`UUT_RESULT` = `MyDB`.`uut_result`.`ID`)))
JOIN `MyDB`.`step_seqcall` ON ((`MyDB`.`step_seqcall`.`STEP_RESULT` = `MyDB`.`step_result`.`STEP_PARENT`)))
WHERE
(`MyDB`.`uut_result`.`ID` = '{f241ea4c-b8da-11e3-a4e2-005056c00008}');

Of course, value for ID must be replaced with your own !