We have a alert table which have deviceID as a foreign key. At present one alert just refers to one devive so things works smoothly with join. The problem start now when one alert now is to refer to more then one device. Thus we plan to keep a separate table with this alertID as foreign keys and devices ID in a new table say alertDetails. The issue now comes with this method is that there is no direct method to gain all the devices for a particular alert. Is there any solution or we have to run two separate queries where first one check if a particular type of alertCode(referring to more then one device) then run next query.
While it is not exactly clear what you need but if you design your tables similar to below then you should be able to retrieve the details that you need.
Then when you query, you could use the following: