Hi,

There is a main table which contains field model_id (value can be 1, 2,3).

Also there are three child tables for each model.

Is it possible to create a select statement which selects depending from the model_id value the right table and use the linked values? Something like if(model_id=1, table1, if (model_id=2, table2, table3).

Mike


>There is a main table which contains field model_id (value can be 1, 2,3).
>
>Also there are three child tables for each model.
>
>Is it possible to create a select statement which selects depending from the model_id value the right table and use the linked values? Something like if(model_id=1, table1, if (model_id=2, table2, table3).

Possibly depending on just what you want. It may be slow, and it will only return a single value for each select eg

SELECT
_ID,
CASE
WHEN _ID MOD 3 = 0 THEN (SELECT _Meal FROM Meals WHERE Meals._ID = 3)
WHEN _ID MOD 4 = 0 THEN (SELECT _Meal FROM Meals WHERE Meals._ID = 4)
WHEN _ID MOD 5 = 0 THEN (SELECT _Meal FROM Meals WHERE Meals._ID = 5)
ELSE 'X'
END
FROM BloodTests

If you can give examples of exactly what you're trying to achieve there may well be a better answer

Roy Lambert

Hopefully this clarifies a bit.

Master table contains the following fields.
id, description, model_id, sub_id
1, 'First', 2, 2
2, 'Second', 1, 1
3, 'Base', 1, 3
4, 'Garden', 2,2

Table model_id_1
sub_id, value
1, 12223
2, 44444
3, 32313

Table model_id_2
sub_id, value
1, 8888
2, 5555
3, 9999

Result should be:
Master table contains the following fields.
id, description, model_id, sub_id
1, 'First', 2, 5555
2, 'Second', 1, 12223
3, 'Base', 1, 32313
4, 'Garden', 2, 5555

- Michael

Mike


To achieve that specific scenario the following should work


SELECT
*,
CASE
WHEN model_id = 1 THEN (SELECT Tablemodel_id_1.value FROM Tablemodel_id_1 WHERE Tablemodel_id_1.sub_id = MasterTable.sub_id)
WHEN model_id = 2 THEN (SELECT Tablemodel_id_2.value FROM Tablemodel_id_2 WHERE Tablemodel_id_2.sub_id = MasterTable.sub_id)
WHEN model_id = 3 THEN (SELECT Tablemodel_id_3.value FROM Tablemodel_id_3 WHERE Tablemodel_id_3.sub_id = MasterTable.sub_id)
ELSE -1
END
FROM MasterTable



Roy Lambert

Thank you Roy.

That did the trick.

Regards,

Mike