Table and query design options

Question

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.

Answer 1

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.

create table alert
(
  id int,
  aname varchar(10)
);
create table device
(
  id int,
  dname varchar(10)
);
create table alertDetails
(
  alertId int,
  deviceId int,
  alertDate datetime
);

Then when you query, you could use the following:

select *
from alert a
left join alertdetails ad
  on a.Id = ad.alertid
left join device d
  on ad.deviceid = d.id
where a.id = 1

See SQL Fiddle with a Demo