Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Use different table in select depending from value in field |
Sun, Feb 8 2015 12:04 PM | Permanent Link |
Mike | 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). |
Sun, Feb 8 2015 12:55 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Feb 8 2015 1:21 PM | Permanent Link |
Mike | 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 |
Mon, Feb 9 2015 2:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Feb 9 2015 5:09 AM | Permanent Link |
Mike | Thank you Roy.
That did the trick. Regards, Mike |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |