Solved

Need some help with an SQL Query...

Posted on 2014-11-19
5
112 Views
Last Modified: 2014-11-19
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
0
Comment
Question by:jatkin
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40452402
>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.
0
 
LVL 4

Author Comment

by:jatkin
ID: 40452421
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'
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40452433
>the ID does not appear in the tblAlarms table
Typo, changed e.EquipmentID to e.ID in the below code.

>the condition <> 66 does not seem to include 'null'
Ok.  NULLs can't be compared to anything, so in order for NULLs to appear change the WHERE clause to include ISNULL and a dummy number.
SELECT DISTINCT ID, SerialNo
FROM tblEquipment e
   --- LEFT JOIN means return all rows from e...
   LEFT JOIN tblAlarms a ON e.ID = a.EquipmentID
-- ... and then filter out those rows without a matching 66
WHERE ISNULL(a.AlarmId, 1) <> 66

Open in new window

0
 
LVL 4

Author Closing Comment

by:jatkin
ID: 40452452
Perfect!
Thanks so much for the quick help :-)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40452498
No prob.  Thanks for the grade, good luck with your project.  -Jim
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Simple SQL query from two tables 13 54
Query Help - MSSQL - Averages 5 27
Sql server, import complete table, using vb.net 9 35
divide by zero error 23 16
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question