Link to home
Get AccessLog in
Avatar of Member_2_99151
Member_2_99151

asked on

Need some help with an SQL Query...

HI all,

I have a query that I am struggling with at the moment and wondered if someone could help me?

I have two tables, one with a list of equipment and the other with alarms associated to each equipment.

For example, if I have:

tblEquipment
ID, SerialNo
0, "111"
1, "222"
2, "333"
3, "444"

tblAlarms
EquipmentID, AlarmID
1,66
1,67
2,66
3,33

What I need to do is get a list of all the Equipment that DOES NOT have alarm 66 associated with it.
This should give 0 and 3

Can someone help me please?

Many thanks,

James
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>list of all the Equipment that DOES NOT have alarm 66 associated with it.
This should give 0 and 3
SELECT DISTINCT ID, SerialNo
FROM tblEquipment e
   --- LEFT JOIN means return all rows from e...
   LEFT JOIN tblAlarms a ON e.EquipmentID = a.EquipmentID
-- ... and then filter out those rows without a matching 66
WHERE a.AlarmId <> 66

Open in new window


For more reading on JOINs check out Tim Chapman's article SQL Server: Table Joins Explained!

Also to get your head wrapped around the different JOINS I recommend going to images.google.com and search for 'SQL TABLE JOINS', and check out all the pretty cartoons on joins.
Avatar of Member_2_99151
Member_2_99151

ASKER

I had tried something similar to this, but if the ID does not appear in the tblAlarms table, the condition <> 66 does not seem to include 'null'
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Perfect!
Thanks so much for the quick help :-)
No prob.  Thanks for the grade, good luck with your project.  -Jim