Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Use the JOIN, Luke! –  ypercube Aug 9 '12 at 13:53
    
What type of join works best here surely not normal join right –  newbie14 Aug 9 '12 at 16:58
    
Do you mean "natural join"? –  jcolebrand Aug 9 '12 at 17:25
5  
Can you show some sample data and desired results, so we don't have to reverse engineer a word problem to attempt to help you solve your issue? –  Aaron Bertrand Aug 9 '12 at 17:26

1 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

share|improve this answer
    
I think is very close let me go through first and test and give you and update. –  newbie14 Aug 10 '12 at 17:21
    
so I must specify the alertID which I require to look up the alertDetails table right. I guess then is best to run 2 separate queries right one specifically just for the particular alertID to check for the details? –  newbie14 Aug 12 '12 at 23:19
    
@newbie14 if you take off the WHERE clause on the query, you will return all of the alert details for all alerts. –  bluefeet Aug 14 '12 at 0:46
    
let me try out first and will update your accorddingly. –  newbie14 Aug 16 '12 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.