Solved

Help with SQL Join Query

Posted on 2011-03-24
2
260 Views
Last Modified: 2012-05-11
I'm trying to work with 4 different tables, 3 tables contain alarm information and the other contains clients information.  What i need to do is query the client table to find out which clients have reporting on.  From there i need to query each of the other tables (backup, antivirus, and server) and pull a max alarm level within lets say the past 24 hours.  Here is what I have so far. This works except does not have the 24 hour date range.  If I add the date range in, some clients disappear, which i dont want.  I need the clients to still appear even if they have null values.

The clients table has two columns im working with:
clientid ->the clients name
reportingon -> either true or false

The other 3 tables have the following columns
clientid ->the clients name
alarmlevel -> 1-4 depending on severity - 4 being worse
alarmdate -> The date/time the alarm went off


SELECT     a.Name, MAX(b.alarmlevel) AS BULVL, MAX(c.alarmlevel) AS AVLVL, MAX(d.alarmlevel) AS SVLVL FROM Clients AS a
          LEFT OUTER JOIN Backups AS b ON a.clientid= b.clientid
          LEFT OUTER JOIN Antivirus AS c ON a.clientid= c.clientid
          LEFT OUTER JOIN Server AS d ON a.clientid = d.clientid
WHERE     (a.reportingon = 'True')
GROUP BY a.clientid
ORDER BY a.clientid
0
Comment
Question by:grizzjeeper
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 35210258
try like this.
SELECT     a.Name, MAX(b.alarmlevel) AS BULVL, MAX(c.alarmlevel) AS AVLVL, MAX(d.alarmlevel) AS SVLVL FROM Clients AS a
          LEFT OUTER JOIN Backups AS b ON a.clientid= b.clientid and b.Datefield > dateadd(hour,-24,getdate())
          LEFT OUTER JOIN Antivirus AS c ON a.clientid= c.clientid and c.Datefield > dateadd(hour,-24,getdate())
          LEFT OUTER JOIN Server AS d ON a.clientid = d.clientid and d.Datefield > dateadd(hour,-24,getdate())
WHERE     (a.reportingon = 'True')
GROUP BY a.clientid 
ORDER BY a.clientid

Open in new window

0
 

Author Closing Comment

by:grizzjeeper
ID: 35210433
Thank you!  exactly what i was looking for.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

713 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